Forum Stats

  • 3,733,249 Users
  • 2,246,737 Discussions
  • 7,856,634 Comments

Discussions

calculate subtotal

bamboo123
bamboo123 Member Posts: 24
edited July 2008 in SQL & PL/SQL
HI, I have

ID Grade Score
2 B 3
2 A 2
2 B 1
1 A 3
1 A 3

I want to display just the subtotal and keep everything the same like this:

ID Grade Score
2 B 3
2 A 2
2 B 1
Subtotal 6
1 A 3
1 A 3
subtotal 6

I tried this but it didn't display the way I want it.
select id,case grouping(id)
when 0 then grade else 'SubTotal'
end grade,
sum(score) score
from table
group by rollup(id,grade)

Thanks,

Comments

  • Sergey Babichev
    Sergey Babichev Member Posts: 262
    edited July 2008
    Try like this:
    with 
    t as
    (
    select 2 as id, 'B' as grade, 3 as score from dual union all
    select 2 as id, 'A' as grade, 2 as score from dual union all
    select 2 as id, 'B' as grade, 1 as score from dual union all
    select 1 as id, 'A' as grade, 3 as score from dual union all
    select 1 as id, 'A' as grade, 3 as score from dual
    )
    select decode(grouping_id(id,grade,score,rownum),0,id) as id,
    decode(grouping_id(id,grade,score,rownum),
    0,grade,
    7,'Subtotal (ID '||ID||')',
    'TOTAL') as grade,
    sum(score) as score
    from t
    group by grouping sets((),(id),(id,grade,score,rownum))
    Query finished, retrieving results...
    ID       GRADE         SCORE
    -- --------------- -----
    1 A 3
    1 A 3
    Subtotal (ID 1) 6
    2 A 2
    2 B 1
    2 B 3
    Subtotal (ID 2) 6
    TOTAL 12
    8 row(s) retrieved
  • bamboo123
    bamboo123 Member Posts: 24
    Thank you so much! it works!
This discussion has been closed.