3-42 rollUpで各値と小計と総合計も表示

SQLパズル

AmountTable
dayC        Val
----------  ---
2010-06-01   10
2010-06-13   20
2010-06-23   60
2010-07-11  100
2010-07-13  200
2010-07-22  500

月ごとの小計と総合計を表示する。

出力結果
gr              Val
--------------  ---
2010-06-01       10
2010-06-13       20
2010-06-23       60
subTotal of 06   90
2010-07-11      100
2010-07-13      200
2010-07-22      500
subTotal of 07  800
Total           890

データ作成スクリプト

create table AmountTable(dayC,Val) as
select date '2010-06-01', 10 from dual union all
select date '2010-06-13', 20 from dual union all
select date '2010-06-23', 60 from dual union all
select date '2010-07-11',100 from dual union all
select date '2010-07-13',200 from dual union all
select date '2010-07-22',500 from dual;

SQL

--■■■grouping setsを使う方法■■■
select case grouping_ID(mon,dayC)
       when 2+1 then 'total'
       when 0+1 then 'Sub-Total of ' || 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 AmountTable)
group by grouping sets((),mon,dayC)
order by max(dayC),grouping_ID(dayC,mon);

--■■■rollUpを使う方法■■■
select case grouping_ID(mon,dayC)
       when 2+1 then 'total'
       when 0+1 then 'Sub-Total of ' || 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 AmountTable)
group by rollUp(mon,dayC)
order by max(dayC),grouping_ID(dayC,mon);

解説

事前にインラインビューで加工した列に列別名をつけておいてから、
grouping setsやrollUpを使うといいでしょう。

grouping setsとrollUpは、脳内でのイメージしやすさで使い分けるといいでしょう。

rollUpは、
rollUp(年,四半期,月,上旬とか下旬,日)
といった集計を行うときに特に便利ではあります。

10-293  TotalとGrandTotalを求める
US-OTN --- I like grouping sets

---