10-86 特定の区分のみサマリ

SQLパズル

テーブル
区分　金額
----  ----
   1   500
   1   300
   1   300
   2   450
   3   550
   6   100
   6   200
   7   320
   7   500

区分が1から7まであり、
1から5の場合は、サマリするが、
6か7の場合は、サマリしないといった出力をする

出力結果
区分　金額
----  ----
   1  1100
   2   450
   3   550
   6   100
   6   200
   7   320
   7   500

SQL

--■■■分析関数を使う方法■■■
select distinct 区分,
sum(金額) over(partition by 区分,case when 区分 in(6,7) then RowNum end) as 金額
from (select 1 as 区分,500 as 金額 from dual
union select 1,300 from dual
union select 1,300 from dual
union select 2,450 from dual
union select 3,550 from dual
union select 6,100 from dual
union select 6,200 from dual
union select 7,320 from dual
union select 7,500 from dual)
order by 区分,金額;

--■■■グループ化する方法■■■
select 区分,sum(金額) as 金額
from (select 1 as 区分,500 as 金額 from dual
union select 1,300 from dual
union select 1,300 from dual
union select 2,450 from dual
union select 3,550 from dual
union select 6,100 from dual
union select 6,200 from dual
union select 7,320 from dual
union select 7,500 from dual)
group by 区分,case when 区分 in(6,7) then RowNum end
order by 区分,金額;

解説

case式で、区分が6か7かチェックしてます

---