SELECT句でCASE

以下の表からCASE句を使って部署毎、性別毎に30歳未満、30歳以上の

社員数を集計する。

社員ID	部署	性別	年齢
1	Creater	male	32
2	Creater	male	30
3	Creater	female	23
4	Valuer	male	45
5	Valuer	male	50
6	Apointer	female	40
7	Apointer	female	42
8	Apointer	male	52
9	Apointer	male	34
10	Apointer	female	41
11	Getter	male	29
12	Getter	female	36

SELECT
dept,COUNT(*) AS '合計',
SUM(CASE WHEN age < 30 THEN 1 ELSE 0 END) AS '合計(30未満)',
SUM(CASE WHEN sex ='male' AND age < 30 THEN 1 ELSE 0 END) AS '男性(30未満)',
SUM(CASE WHEN sex ='female' AND age < 30 THEN 1 ELSE 0 END) AS '女性(30未満)',
SUM(CASE WHEN age >= 30 THEN 1 ELSE 0 END) AS '合計(30以上)',
SUM(CASE WHEN sex ='male' AND age >= 30 THEN 1 ELSE 0 END) AS '男性(30以上)',
SUM(CASE WHEN sex ='female' AND age >= 30 THEN 1 ELSE 0 END) AS '女性(30以上)'
FROM test
GROUP BY dept

■実行結果

dept	合計	合計(30未満)	男性(30未満)	女性(30未満)	合計(30以上)	男性(30以上)	女性(30以上)
Apointer	5	0	0	0	5	2	3
Creater	3	1	0	1	2	2	0
Getter	2	1	1	0	1	0	1
Valuer	2	0	0	0	2	2	0