Rollup

[Sameer]

Hi,

I don't know how come the statement got cut..

anyways.. heres again

I used "CUBE" to get sum of individula subscribers as well as sum of all subscribers for weekdays.. If you see the output, it shows sum of subscribers, sum of subscribers for weekdays and total sum..

If I use rollup, I won't able to get sum of subscribers as well as sum for subscribers for weekday..

I use decode just to sort by weekday.. There might be some other way of sorting by weekday..

Using ROLLUP

SELECT subscriber_name,
DECODE(to_char(datum,'D'),1,'Mon',2,'Tue',3,'Wed',
4,'Thu',5,'Fri',6,'Sat',7,'Sun', 'Tot:'), SUM(hours) hours
FROM subscriber 
GROUP BY Rollup (subscriber_name, to_char(datum,'D'))


SUBSCRIBER DECO      HOURS
---------- ---- ----------
Joe        Mon           8
           Tue           2
           Wed           3
           Thu           7
           Fri           4
           Sat           8
           Sun           1
           Tot:         33 - Joe's total

John       Mon           2
           Tue           5
           Wed           3
           Thu           7
           Fri           1
           Sat           9
           Sun           4
           Tot:         31 - John's total

Lis        Mon          15
           Tue           1
           Wed          13
           Thu           8
           Fri           2
           Sat          17
           Sun           3
           Tot:         59 - Lis' total

Martin     Mon           5
           Tue           1
           Wed           3
           Thu           8
           Fri           2
           Sat           7
           Sun           3
           Tot:         29 - Martin's total

           Tot:        152 - Grand total

Using CUBE

SQL> SELECT subscriber_name,
  2  DECODE(to_char(datum,'D'),1,'Mon',2,'Tue',3,'Wed',
  3  4,'Thu',5,'Fri',6,'Sat',7,'Sun', 'Tot:'), SUM(hours) hours
  4  FROM subscriber
  5  GROUP BY cube (subscriber_name, to_char(datum,'D'))
  6  ;

SUBSCRIBER DECO      HOURS
---------- ---- ----------
Joe        Mon           8
           Tue           2
           Wed           3
           Thu           7
           Fri           4
           Sat           8
           Sun           1
           Tot:         33 - Joe's total

John       Mon           2
           Tue           5
           Wed           3
           Thu           7
           Fri           1
           Sat           9
           Sun           4
           Tot:         31 - John's total

Lis        Mon          15
           Tue           1
           Wed          13
           Thu           8
           Fri           2
           Sat          17
           Sun           3
           Tot:         59 - Lis' total

Martin     Mon           5
           Tue           1
           Wed           3
           Thu           8
           Fri           2
           Sat           7
           Sun           3
           Tot:         29 - Martin's total

           Mon          30
           Tue           9
           Wed          22
           Thu          30
           Fri           9
           Sat          41
           Sun          11 - Weekday's total for all subscribers

           Tot:        152 - Grand total

HTH

Sameer