How do you get average sums in SQL (tiered aggregation)?

Question

I have a simplified xx table 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 value (for all times) of the sums (for all identifiers) of the values.

As an example of a table I have (with consecutive identical values, muffled 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 amounts I want to receive:

 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 was expected.

Now what I want is a query that will also average these values ​​over time, providing me with:

 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 use DB2 for z / OS, but I would prefer standard SQL if possible.

Answer 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 

Answer 2

What 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