-
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
-
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
Last edited by Sameer; 11-08-2002 at 07:52 AM.
-
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
Last edited by Sameer; 11-11-2002 at 12:59 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|