How do you get average of sums in SQL (multi-level aggregation)?
- by paxdiablo
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-06-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.
