Rollup

I've asked about this before but have had no success with what was suggested.

I have an sql script that collects stats on our users on a daily basis. The problem I have is that I can get it to add up the columns per day but I need to then be able to add up all the days to give me a weekly total and I don't know how! I have to spend hours doing it in excel, anybody got any ideas??

E.G
Mon Tue Wed Thu Fri Sat Sun
Jones 0 1 2 3 4 5 6
Smith 3 2 0 1 3 1 0
---------------------------
3 3 2 4 7 6 6 I need a grand total of this.

I've tried using rollup as suggested but it doesn't work, just appears to repeat the lines.

This is my sql

break on subscriber skip 1 on username on job on id
compute sum of Monday Tuesday Wednesday Thursday Friday Saturday Sunday on subscriber
select initcap(s.subscriber_name) subscriber
,initcap(su.user_name) username
,su.job_title job
, su.public_id id
, stats_by_week(su.public_id, trunc(sysdate -7)) Monday
, stats_by_week(su.public_id, trunc(sysdate -6)) Tuesday
, stats_by_week(su.public_id, trunc(sysdate -5)) Wednesday
, stats_by_week(su.public_id, trunc(sysdate -4)) Thursday
, stats_by_week(su.public_id, trunc(sysdate -3)) Friday
, stats_by_week(su.public_id, trunc(sysdate -2)) Saturday
, stats_by_week(su.public_id, trunc(sysdate -1)) Sunday
from subscribers s
,subscriber_users su
,security_log sl
where sl.PUBLIC_ID(+) = su.PUBLIC_ID
and su.SUBSCRIBER_ID = s.SUBSCRIBER_ID
and sl.MODULE_REF(+) like 'VIEW%'
and s.subscriber_id!= 1'
group by s.subscriber_name, su.user_name,su.job_title, su.public_id
order by s.subscriber_name

somehow I need to rollup monday - sunday.

Any examples of how to do this would be greatly appreciated.

Thanks, Ali

:confused:

Still waiting for advice - anyone got any suggestions??!

Though this is not in the same format as you expect, but gives the required result... Individual total, total on days and grand total..

Code:

---

SQL>l
  1  SELECT subscriber_name,
  2  DECODE(to_char(datum,'D'),1,'Mon',2,'Tue',3,'Wed',4,'Thu',5,'Fri',6,'Sat',7,'Sun', 'To
  3  SUM(hours) hours
  4  FROM subscriber
  5* GROUP BY CUBE (subscriber_name, to_char(datum,'D'))
SQL> /

SUBSCRIBER DAY        HOURS
---------- ------ ----------
Joe        Mon            8
          Tue            2
          Wed            3
          Thu            7
          Fri            4
          Sat            8
          Sun            1
          Total          33

John      Mon            2
          Tue            5
          Wed            3
          Thu            7
          Fri            1
          Sat            9
          Sun            4
          Total          31

Lis        Mon            15
          Tue            1
          Wed            13
          Thu            8
          Fri            2
          Sat            17
          Sun            3
          Total          59

Martin    Mon            5
          Tue            1
          Wed            3
          Thu            8
          Fri            2
          Sat            7
          Sun            3
          Total          29

          Mon            30
          Tue            9
          Wed            22
          Thu            30
          Fri            9
          Sat            41
          Sun            11

          Total        152


40 rows selected.

SQL>

---

Sameer

Hi Sameer

Thanks for your reply but it looks like a bit is missing on the decode line? not sure but I can't make it work anyway!

Can you briefly explain what its doing and why you have used cube instead of rollup?

Many thanks,

Ali

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

Code:

---

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

Code:

---

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