Forum Stats

  • 3,757,562 Users
  • 2,251,245 Discussions
  • 7,869,867 Comments

Discussions

How to have Subtotal on Months in Query

kamranpathan
kamranpathan Member Posts: 504
edited Jul 8, 2010 8:45AM in SQL & PL/SQL
Dear Users I've data like this:

Date Val
01-Jun-10 10
02-Jun-10 15
03-Jun-10 05
01-Jul-10 10
02-Jul-10 15

Now how can I've sub-total on each monthe.g June & July in above example and a grand total at the end???
«1

Answers

  • 780914
    780914 Member Posts: 1,095
    edited Jul 7, 2010 7:30AM
    Use Cube and Rollup in Group by clause to get subtotals at desired levels
    with c as
    (
    select to_date('01-Jun-10','DD-MM-YYYY') d, 10 s from dual union all
    select to_date('02-Jun-10','DD-MM-YYYY'), 5 from dual union all
    select to_date('03-Jun-10','DD-MM-YYYY'), 10 from dual union all
    select to_date('01-Jul-10','DD-MM-YYYY'), 15 from dual union all
    select to_date('05-Jul-10','DD-MM-YYYY'), 20 from dual)
    select decode(grouping(to_char(d,'MONTH')),1,'Total',to_char(d,'MONTH')) m,sum(s) s
    from c
    group by rollup( to_char(d,'MONTH'))
    Edited by: SBH on Jul 7, 2010 4:30 AM
  • 743632
    743632 Member Posts: 52
    edited Jul 7, 2010 7:33AM
    You can try this...

    SELECT to_char(Date,'mon') ,N,SUM(val) FROM TEST GROUP BY rollup(to_char(Date,'mon'),val)
    /

    Edited by: user00001 on 07-Jul-2010 04:32
  • kamranpathan
    kamranpathan Member Posts: 504
    Dear Guru...
    Although urs query has given the required result however it would be perfect if it mark the sub-total row differently i.e instead of month name there should be words like 'Sub-Total' and 'Grand-Total' at the end.
  • 780914
    780914 Member Posts: 1,095
    Have you tried this
    with c as
    (
    select to_date('01-Jun-10','DD-MM-YYYY') d, 10 s from dual union all
    select to_date('02-Jun-10','DD-MM-YYYY'), 5 from dual union all
    select to_date('03-Jun-10','DD-MM-YYYY'), 10 from dual union all
    select to_date('01-Jul-10','DD-MM-YYYY'), 15 from dual union all
    select to_date('05-Jul-10','DD-MM-YYYY'), 20 from dual)
    select decode(grouping(to_char(d,'MONTH')),1,'Total','Sub Totoal for '||to_char(d,'MONTH')) m,sum(s) s
    from c
    group by rollup( to_char(d,'MONTH'))
  • kamranpathan
    kamranpathan Member Posts: 504
    Yes...I've tried but its marking all the values rows as "Sub Totoal for ", how can I distinguish values from subtotals???
  • 780914
    780914 Member Posts: 1,095
    Are you not getting Month as 'June','July'
    This will distinguish the results
    SQL> with c as
      2  (
      3  select to_date('01-Jun-10','DD-MM-YYYY') d, 10 s from dual union all
      4  select to_date('02-Jun-10','DD-MM-YYYY'), 5 from dual union all
      5  select to_date('03-Jun-10','DD-MM-YYYY'), 10 from dual union all
      6  select to_date('01-Jul-10','DD-MM-YYYY'), 15 from dual union all
      7  select to_date('05-Jul-10','DD-MM-YYYY'), 20 from dual)
      8  select decode(grouping(to_char(d,'MONTH')),1,'Total','Sub Totoal for '||to_char(d,'MONTH')) m,s
    um(s) s
      9  from c
     10  group by rollup( to_char(d,'MONTH'))
     11  /
    
    M                                 S
    ------------------------ ----------
    Sub Totoal for JULY              35
    Sub Totoal for JUNE              25
    Total                            60
  • kamranpathan
    kamranpathan Member Posts: 504
    Dear SBH...yours query is ok but it lacks information coz I need it in the following way:

    Date Val
    01-Jun-10 10
    02-Jun-10 15
    03-Jun-10 05
    Sub-Total for Jun30
    01-Jul-10 10
    02-Jul-10 15
    Sub-Total for Jul 25
    G.Total 55
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Jul 7, 2010 10:22AM
    I like grouping sets B-)
    And simple case expression and grouping_ID
    with c as(
    select date '2010-06-01' dayc, 10 Val from dual union all
    select date '2010-06-02',  5 from dual union all
    select date '2010-06-03', 10 from dual union all
    select date '2010-07-01', 15 from dual union all
    select date '2010-07-02', 20 from dual)
    select case grouping_ID(mon,dayc)
           when 2+1 then 'total'
           when 0+1 then 'Sub-Total for' || mon
           else dayc end as gr,
    sum(val) as Val
    from (select to_char(dayc,'yyyy-mm-dd') as dayc,
          to_char(dayc,'mm') as mon,val
          from c)
    group by grouping sets((),mon,dayc)
    order by max(dayc),grouping_ID(dayc,mon);
    
    GR               VAL
    ---------------  ---
    2010-06-01        10
    2010-06-02         5
    2010-06-03        10
    Sub-Total for06   25
    2010-07-01        15
    2010-07-02        20
    Sub-Total for07   35
    total             60
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Jul 7, 2010 5:32PM
    Oh we can use rollup too, and this is more simple :8}
    with c as(
    select date '2010-06-01' dayc, 10 Val from dual union all
    select date '2010-06-02',  5 from dual union all
    select date '2010-06-03', 10 from dual union all
    select date '2010-07-01', 15 from dual union all
    select date '2010-07-02', 20 from dual)
    select case grouping_ID(mon,dayc)
           when 2+1 then 'total'
           when 0+1 then 'Sub-Total for' || mon
           else dayc end as gr,
    sum(val) as Val
    from (select to_char(dayc,'yyyy-mm-dd') as dayc,
          to_char(dayc,'mm') as mon,val
          from c)
    group by rollup(mon,dayc)
    order by max(dayc),grouping_ID(dayc,mon);
  • 698658
    698658 Member Posts: 769
    Hi,
    please clarify this to me
    select case grouping_ID(mon,dayc)
           when 2+1 then 'total'
           when 0+1 then 'Sub-Total for' || mon
           else dayc end as gr,
    Whats the calculation formula behind grouping id ?
    Regards.
    Greg
This discussion has been closed.