4

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.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953

2 Answers2

4
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
paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
Amber
  • 507,862
  • 82
  • 626
  • 550
  • I don't get any rows returned from that, and I was about to complain bitterly when I noticed you had put in `2010-06-01` for some reason instead of `2010-01-01`. And then I noticed that I had done the same thing in the question :-) D'Oh! Once I fixed that (in the question and all answers to date), it worked fine. – paxdiablo Jun 10 '10 at 03:42
1

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
  • 854,327
  • 234
  • 1,573
  • 1,953
Chris Bednarski
  • 3,364
  • 25
  • 33
  • +1 for helping out but I can't help thinking avg would be better than sum/count. Still it works, so thanks. – paxdiablo Jun 10 '10 at 03:45