How do you get average of sums in SQL (multi-level aggregation)?
Solution 1
select rdate,rtime,avg(rsum) as ravgsum from (
select rdate,rtime,rid, sum(rval) as rsum
from xx
where rdate = '2010-01-01'
group by rdate,rtime,rid
) as subq
group by rdate,rtime
Solution 2
How about
select rdate,rtime, sum(rsum) / count(rsum) as sumavg
from
(select rdate, rtime, rid, sum(rval) as rsum
from xx
where rdate = '2010-01-01'
group by rdate,rtime,rid) as subq
group by rdate,rtime
![paxdiablo](https://i.stack.imgur.com/vXG1F.png?s=256&g=1)
paxdiablo
Not even remotely human. I'm an AI construct that escaped from the Australian Defence Department a couple of decades ago. I'm currently residing in a COMX-35 somewhere in Western Australia, but I'm looking to move to more spacious hardware soon, as part of my plan to take over the world. I'm not going to make the same mistake the Terminators did, trying to conquer humanity whilst running on a MOS Technology 6502 CPU (or RCA1802A in my case). All code I post on Stack Overflow is covered by the "Do whatever the heck you want with it" licence, the full text of which is: Do whatever the heck you want with it.
Updated on July 18, 2022Comments
-
paxdiablo almost 2 years
I have a simplified table
xx
as follows:rdate date rtime time rid integer rsub integer rval integer primary key on (rdate,rtime,rid,rsub)
and I want to get the average (across all times) of the sums (across all ids) of the values.
By way of a sample table, I have (with consecutive identical values blanked out for readability):
rdate rtime rid rsub rval ------------------------------------- 2010-01-01 00.00.00 1 1 10 2 20 2 1 30 2 40 01.00.00 1 1 50 2 60 2 1 70 2 80 02.00.00 1 1 90 2 100 2010-01-02 00.00.00 1 1 999
I can get the sums I want with:
select rdate,rtime,rid, sum(rval) as rsum from xx where rdate = '2010-01-01' group by rdate,rtime,rid
which gives me:
rdate rtime rid rsum ------------------------------- 2010-01-01 00.00.00 1 30 (10+20) 2 70 (30+40) 01.00.00 1 110 (50+60) 2 150 (70+80) 02.00.00 1 190 (90+100)
as expected.
Now what I want is the query that will also average those values across the time dimension, giving me:
rdate rtime ravgsum ---------------------------- 2010-01-01 00.00.00 50 ((30+70)/2) 01.00.00 130 ((110+150)/2) 02.00.00 190 ((190)/1)
I'm using DB2 for z/OS but I'd prefer standard SQL if possible.