7-21 sum関数とcase式

SQLパズル

productsテーブル
week  product  total
----  -------  -----
   1     eggs      7
   1     beef      4
   2     eggs      9
   3     soup      5
   4     beef      3

productsテーブルから、
weekごとのbeefの数と、eggsの数と、
beefの数とeggsの数の合計を出力する。

出力結果
week  beef  eggs  total
----  ----  ----  -----
   1     4     7     11
   2     0     9      9
   3     0     0      0
   4     3     0      3

データ作成スクリプト

create table products(
week    number(1),
Product char(4),
total   number(1));

insert into products values(1,'eggs',7);
insert into products values(1,'beef',4);
insert into products values(2,'eggs',9);
insert into products values(3,'soup',5);
insert into products values(4,'beef',3);
commit;

SQL

select week,
sum(case when Product = 'beef' then total else 0 end) as beef,
sum(case when Product = 'eggs' then total else 0 end) as eggs,
sum(case when Product in('beef','eggs') then total else 0 end) as total
from products
group by week
order by week;

解説

sum関数とcase式を組み合わせると、
条件に応じた集計ができます。

---