10-131 最大値を求めてクロス集計

SQLパズル

CodeTable
Code   Val  KIJUN
----  ----  -----
 100  1000   1000
 100  2000   4000
 200  2000      0
 200  1000   3000
 200  1000   5000
 300  1000   1000
 300  1000   3000

Codeごとの、Valの合計と、KIJUNの最大値を求めて
さらに総合計を求める

出力結果
 Code   Val  KIJUN
-----  ----  -----
  100  3000   4000
  200  4000   5000
  300  2000   3000
Total  9000  12000

データ作成スクリプト

create table CodeTable as
select '100' as Code,1000 as Val,1000 as KIJUN from dual
union all select '100',2000,4000 from dual
union all select '200',2000,   0 from dual
union all select '200',1000,3000 from dual
union all select '200',1000,5000 from dual
union all select '300',1000,1000 from dual
union all select '300',1000,3000 from dual;

SQL

--■■■分析関数を使う方法1■■■
select
decode(grouping(Code),1,'Total',Code) as Code,
sum(Val) as Val,
case when grouping(Code) = 1
     then sum(decode(grouping(Code),0,max(KIJUN))) over()
     else max(KIJUN) end as KIJUN
from CodeTable
group by rollup(Code)
order by Code;

--■■■分析関数を使う方法2■■■
select
decode(grouping(Code),1,'Total',Code) as Code,
sum(Val) as Val,
case when grouping(Code) = 1
     then sum(max(KIJUN)) over() - max(KIJUN)
     else max(KIJUN) end as KIJUN
from CodeTable
group by rollup(Code)
order by Code;

--■■■インラインビューを使う方法■■■
select decode(grouping(Code),1,'Total',Code) as Code,
sum(Val) as Val,
sum(KIJUN) as KIJUN
  from (select Code,
        sum(Val) Val,
        max(KIJUN) KIJUN
          from CodeTable
        group by Code)
group by rollup(Code)
order by Code;

解説

インラインビューを使う方法が、
分かりやすいと思います

---