DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Rollup

  1. #1
    Join Date
    Mar 2002
    Posts
    25

    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


  2. #2
    Join Date
    Mar 2002
    Posts
    25
    Still waiting for advice - anyone got any suggestions??!

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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.

  4. #4
    Join Date
    Mar 2002
    Posts
    25
    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

  5. #5
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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
  •  


Click Here to Expand Forum to Full Width