10-301 rollupで複合列(Composite Columns)指定

SQLパズル

ValT
ID     ValA  ValB  ValC
-----  ----  ----  ----
AAAAA     1     2     3
AAAAA     2     4     8
BBBBB     3     6     9
CCCCC     4     8    12

横計と縦計を求める。

出力結果
ID     ValA  ValB  ValC  RowSum
-----  ----  ----  ----  ------
AAAAA     1     2     3       6
AAAAA     2     4     8      14
BBBBB     3     6     9      18
CCCCC     4     8    12      24
Total    10    20    32      62

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table ValT(ID,ValA,ValB,ValC) as
select 'AAAAA',1,2, 3 from dual union
select 'AAAAA',2,4, 8 from dual union
select 'BBBBB',3,6, 9 from dual union
select 'CCCCC',4,8,12 from dual;

SQL

--■■■複合列指定を使う方法■■■
select decode(grouping(ID),0,ID,'Total') as ID,
sum(ValA) as ValA,sum(ValB) as ValB,sum(ValC) as ValC,
sum(ValA+ValB+ValC) as RowSum
  from ValT
group by rollup((ID,ValA,ValB,ValC));

--■■■複合列指定を使わない方法1■■■
select decode(grouping(RowID),0,max(ID),'Total') as ID,
sum(ValA) as ValA,sum(ValB) as ValB,sum(ValC) as ValC,
sum(ValA+ValB+ValC) as RowSum
  from ValT
group by rollUp(RowID)
order by ID;

--■■■複合列指定を使わない方法2■■■
select decode(grouping(RowNum),0,max(ID),'Total') as ID,
sum(ValA) as ValA,sum(ValB) as ValB,sum(ValC) as ValC,
sum(ValA+ValB+ValC) as RowSum
  from ValT
group by rollUp(RowNum)
order by ID;

解説

複合列 (Composite Columns) と呼ばれる記述法です。
複合列ごとで集計したい時に使えます。

create table rollT(ID1,ID2,Val) as
select 1,2,10 from dual union
select 1,3,20 from dual union
select 1,4,40 from dual union
select 2,1,80 from dual union
select 2,2,160 from dual;

select ID1,ID2,sum(Val)
  from rollT
group by rollup ((ID1,ID2));

 ID1   ID2   SUM(VAL)
----  ----  ---------
   1     2         10
   1     3         20
   1     4         40
   2     1         80
   2     2        160
null  null        310

select ID1,ID2,sum(Val)
  from rollT
group by rollup (ID1,ID2);

 ID1   ID2   SUM(VAL)
----  ----  ---------
   1     2         10
   1     3         20
   1     4         40
   1  null         70
   2     1         80
   2     2        160
   2  null        240
null  null        310

複合列
Composite Columns


group by rollup ((ID1,ID2))
は、
group by grouping sets((ID1,ID2),())
と同じ意味です。

SQL> select ID1,ID2,sum(Val),count(*) over()
  2    from rollT
  3  group by grouping sets((ID1,ID2),())
  4  minus
  5  select ID1,ID2,sum(Val),count(*) over()
  6    from rollT
  7  group by rollup ((ID1,ID2));

レコードが選択されませんでした。

7-32 キーブレイク時に、指定行を出力その1
10-312 複合列指定を使ったクロス集計

---