7-49 小計の降順にソート

SQLパズル

売上テーブル
Code  SubCode   Month  Amount
----  -------  ------  ------
   A        1  200503      10
   A        1  200504      10
   A        2  200503      15
   A        2  200504      15
   B        1  200503      10
   B        1  200504      15
   B        2  200504      15
   C        1  200503      30
   C        1  200504      30
   D        1  200504      30
   D        2  200504      40

売上テーブルのコードごとの、
Amountの合計(小計)と、総合計を出力する。

ソートは、
総合計が最後で、
Codeごとの、Amountの小計の降順とし、
同一Code内では、SubCodeの昇順で、小計が最後とする。

出力結果
Code    SubCode  Amount
------  -------  ------
     D        1      30
     D        2      40
     D     小計      70
     C        1      60
     C     小計      60
     A        1      20
     A        2      30
     A     小計      50
     B        1      25
     B        2      15
     B     小計      40
総合計     null     220

データ作成スクリプト

create table 売上(
Code    char(1),
SubCode char(1),
Month   char(6),
Amount  number(2));

insert into 売上 values('A','1','200503',10);
insert into 売上 values('A','1','200504',10);
insert into 売上 values('A','2','200503',15);
insert into 売上 values('A','2','200504',15);
insert into 売上 values('B','1','200503',10);
insert into 売上 values('B','1','200504',15);
insert into 売上 values('B','2','200504',15);
insert into 売上 values('C','1','200503',30);
insert into 売上 values('C','1','200504',30);
insert into 売上 values('D','1','200504',30);
insert into 売上 values('D','2','200504',40);
commit;

SQL

select decode(grouping(Code),1,'総合計',Code) as Code,
case when 1 = all(grouping(Code),grouping(SubCode)) then null
     when grouping(SubCode) = 1 then '小計'
     else SubCode end as SubCode,
sum(Amount) as Amount
from 売上
group by rollup(Code,SubCode)
order by
decode(Code,'総合計',1,0),
sum(Amount) over(partition by Code) desc,
nullif(SubCode,'小計');

解説

order by句でsum関数を使って、
Codeごとの、Amountの小計の2倍を取得して、ソートしてます。

数学の不等式の公式
X > Y ⇔ X*2 > Y*2
を使ってます。

---