10-293 TotalとGrandTotalを求める

SQLパズル

copyEmp
deptNO  empNO  sal
------  -----  -----
10      7782    2450
10      7839    5000
10      7934    1300
20      7369     800
20      7566    2975
20      7788    3000
20      7876    1100
20      7902    3000
30      7499    1600
30      7521    1250
30      7654    1250
30      7698    2850
30      7844    1500
30      7900     950

deptNOごとのsalのTotalと、GrandTotalを
以下の形式で求める。

出力結果
descr                deptNO  empNO  sal
-------------------  ------  -----  -----
                     10      7782    2450
                     10      7839    5000
                     10      7934    1300
Total Of dept - 10   10      null    8750
                     20      7369     800
                     20      7566    2975
                     20      7788    3000
                     20      7876    1100
                     20      7902    3000
Total Of dept - 20   20      null   10875
                     30      7499    1600
                     30      7521    1250
                     30      7654    1250
                     30      7698    2850
                     30      7844    1500
                     30      7900     950
Total Of dept - 30   30      null    9400
Grand Total of dept  null    null   29025

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

データ作成スクリプト

create table copyEmp as
select deptNO,empNO,sal
  from scott.emp;

SQL

col descr for a20

--■■■rollupを使う方法■■■
select case grouping_ID(deptno, empno)
       when 0+1 then 'Total Of dept - ' || deptno
       when 2+1 then 'Grand Total of dept'
       else ' ' end descr,
deptno,empno,sum(sal) as sal
  from copyEmp
 group by rollup(deptno, empno)
order by deptno, empno;

--■■■model句を使う方法1(10g以降)■■■
select descr,deptno,empno,sal
  from copyEmp
 model
 dimension by(RowNum as rn,deptno)
 measures(cast(' ' as varChar2(20)) as descr,empno,sal)
 rules(
 upsert all descr[0,any] = 'Total Of dept - ' || cv(deptno),
 descr[null,null] = 'Grand Total of dept',
 sal[null,null] = sum(sal)[any,any],
 sal[0,any] order by rn,deptno = sum(sal)[any,cv()])
order by deptno,empno;

--■■■model句を使う方法2(10g以降)■■■
select descr,deptno,empno,sal
  from copyEmp
 model
 dimension by(RowNum as rn,deptno)
 measures(cast(' ' as varChar2(20)) as descr,empno,sal)
 rules(
 upsert all descr[0,any] = 'Total Of dept - ' || cv(deptno),
 descr[null,null] = 'Grand Total of dept',
 sal[0,any] order by rn,deptno = sum(sal)[any,cv()],
 sal[null,null] = sum(sal)[0,any])
order by deptno,empno;

解説

単純case式は、条件式の2度書きを防ぐといった用途に使えます。
grouping_ID関数の結果である、
grouping_ID(deptno, empno) は
0+1
2+1
のように記述すると読みやすいです。

rollupを使わずに、model句でも可能です。
パーティションを切ると最後にsumで集計できなくなることに注意です。

model句を使う方法1よりも、
model句を使う方法2のほうが導出順序が分かりやすいでしょう。

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

---