10-312 複合列指定を使ったクロス集計

SQLパズル

crossT
org  account  amount
---  -------  ------
A1       101     100
A2       101      20
A2       102      44
A3       102      34
A3       103      50
A4       103      29
A5       103      32
A4       120      10

accountごとで、amountの小計を求めて、
下記の形式で出力する。

出力結果
org   amount                     percentage
----  -------------------------  ----------
A1    100                        83.33%
A2    20                         16.67%
null  Total by 101 Account: 120  100%
A2    44                         56.41%
A3    34                         43.59%
null  Total by 102 Account: 78   100%
A3    50                         45.05%
A4    29                         26.13%
A5    32                         28.83%
null  Total by 103 Account: 111  100%
A4    10                         100%
null  Total by 120 Account: 10   100%

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

データ作成スクリプト

create table crossT(org,account,amount) as
select 'A1', 101,100 from dual union all
select 'A2', 101, 20 from dual union all
select 'A2', 102, 44 from dual union all
select 'A3', 102, 34 from dual union all
select 'A3', 103, 50 from dual union all
select 'A4', 103, 29 from dual union all
select 'A5', 103, 32 from dual union all
select 'A4', 120, 10 from dual;

SQL

col amount for a30
col percentage for a40

select org,
case grouping(org)
when 1 then 'Total by ' || to_char(account)
         || ' Account: ' || to_char(sum(amount))
else to_char(amount) end as amount,
to_char(round(100*ratio_to_report(sum(amount))
                  over (partition by account,grouping(org))
             ,2)) || '%' as percentage
  from crossT
group by account,rollup((org,amount))
order by account,org;

解説

rollupで複合列指定を使ってます。

7-32 キーブレイク時に、指定行を出力その1
10-301 rollupで複合列(Composite Columns)指定

---