Averaging Columns from my Result

[clakerley]

Hello Everyone,

I am having difficulty figuring out how to do a query that averages some of the columns of my result set and making that average part of the result.

Here is an example of what I'm talking about. Let's say I have four records on MyTable, as follows:

ProgramName BusinessUnit Q1Rev Q2Rev Q3Rev Q4Rev
----------- ------------ ----- ----- ----- -----
P1 BU1 10 10 0 0
P2 BU1 0 10 10 0
P3 BU2 0 0 10 10
P4 BU2 0 0 10 10


When I run this query:

SELECT BusinessUnit, Sum(Q1Rev) as Q1, Sum(Q2Rev) as Q2, Sum(Q3Rev) as Q3, Sum(Q4Rev) as Q4
FROM MyTable
GROUP BY BusinessUnit

I get the following results:

BusinessUnit Q1 Q2 Q3 Q4
------------ -- -- -- --
BU1 10 20 10 0
BU2 0 0 20 20

What I want to be able to due is add a final column called AvgAnnualRev that has the following results:

BusinessUnit Q1 Q2 Q3 Q4 AvgAnnualRev
------------ -- -- -- -- ------------
BU1 10 20 10 0 53.333
BU2 0 0 20 20 80

AvgAnnual is calculated by taking the average of the NON-ZERO quarterly revenue totals (to get the average quarterly revenue), then multiplying by 4 to get the average annual revenue.

So, the result for BU1 is ((10 + 20 + 10) / 3) * 4 = 53.333
And the result for BU2 is ((20 + 20) / 2) * 4 = 80

My question is, how do I do this in SQL? As it stands, I end up having to put the results of my first query (without AvgAnnualRev) into a spreadsheet and then manually use my spreadsheet program to calculate the AvgAnnualRev column.

Thanks in advance for any help!

clakerley

 

[r937]

select BusinessUnit
     , Q1, Q2, Q3, Q4
     , case when Q1=0 and Q2=0 and Q3=0 and Q4=0
            then 0 else
         (Q1+Q2+Q3+Q4) * 4
       / (case when Q1<>0 then 1 else 0 end
        + case when Q2<>0 then 1 else 0 end
        + case when Q3<>0 then 1 else 0 end
        + case when Q4<>0 then 1 else 0 end) 
         end
           as AvgAnnualRev
  from (        
       SELECT BusinessUnit
           , Sum(Q1Rev) as Q1
           , Sum(Q2Rev) as Q2
           , Sum(Q3Rev) as Q3
           , Sum(Q4Rev) as Q4
        FROM MyTable
      GROUP 
          BY BusinessUnit
       ) as dt

r937.com | rudy.ca

 

[clakerley]

Thanks! This worked like a charm.

By the way, is the "as dt" toward the end of your statement technically necessary? I accidentally tried it without that, and it gave me the same results as it did when I added it in.

I'm just wondering if including it was technically necessary in ANSI-SQL and my DBMS just happened to handle it correctly without it, or if it's purely optional, like aliasing a column name can be.

Or maybe at helps with the efficiency?

Thanks again!

clakerley

 

[r937]

i don't know whether AS alias is mandatory in standard sql, but i do know that there's at least one dbms which gives you an error if it's omitted (although i cannot remember which one it is), so i just always include it

r937.com | rudy.ca

 

[PHV]

although i cannot remember which one it is
JetSQL (ie MS-Access)

 

[r937]

thanks, phv, but i'm still gonna forget it

and i thought it was mysql ;-)

r937.com | rudy.ca

 

[JarlH]

The AS keyword is really optional.

ISO/IEC 9075-2:2003 (E):
----
7.12 <query specification>
[...]
<derived column>::=<value expression>[ <as clause>]

<as clause>::=[AS ]<column name>
----


/Jarl

 

[dnoeth]

clakerley probably uses Oracle and then you may omit (sometimes) even the alias not only the AS.

Dieter

 

[clakerley]

Actually, I use a DBMS I had never heard of before my current job, called NexusDB. It is an updated and much more stable version of another DBMS I had never heard of called FlashFiler.

If anyone's interested I can post their web site. Is that allowed in this forum? I'm new and am not versed in all the rules yet....

Thanks again,

clakerley