成績と性別毎の人数分布を集計するSQL文は？

質問

SQL文で成績と性別での人数分布を求めるクロス集計関数が出来ず困ってます。

サンプルとしては
学年　性別 評価(ABC)
１年　男　　A
１年　女　　A
１年　男　　A
１年　男　　B
２年　男　　B
２年　女　　C

欲しい出力としては各サンプル毎の人数です。
１年 男-A評価,男B評価,男C評価,女A評価,女B評価,女C評価
２年 男-A評価,男B評価,男C評価,女A評価,女B評価,女C評価
３年 男-A評価,男B評価,男C評価,女A評価,女B評価,女C評価
・
・
・
という具合です。

以前は
SELECT COUNT(*) AS 分布人数, 性別, 成績 FROM 成績
GROUP BY 年齢, 性別, 学年 HAVING 学年 = @がくねん ORDER BY 年齢,成績
というSQL文で１学年毎に抽出していました。
クロス集計で１つのSQL文で出力したいのですがいかがでしょうか？

回答No.2

chukenkenkou · ベストアンサー

表の列構成、元のデータ例、得たい結果例が示されていると、回答する側としても分かりやすいのですけどね。

次のようなSQLでは、いかがでしょうか？

select
学年,
count(case when 性別='m' and 評価='A' then 1 else null end) as 男A,
count(case when 性別='m' and 評価='B' then 1 else null end) as 男B,
count(case when 性別='m' and 評価='C' then 1 else null end) as 男C,
count(case when 性別='w' and 評価='A' then 1 else null end) as 女A,
count(case when 性別='w' and 評価='B' then 1 else null end) as 女B,
count(case when 性別='w' and 評価='C' then 1 else null end) as 女C
from t1
group by 学年
;

お礼

chukenkenkouさんありがとうございました。
ほぼ、このSQL文で欲しい出力が出ました。

回答No.1

t-ka

んーこんなのはどうでしょう、長いですけど構造は単純です。
良ければ質問者さま用に適宜書き換えてみてください。

SELECT '第一学年' AS 学年,
COUNT(CASE WHEN nenndo = '1' and sex = '男' and hyouka = 'A' THEN 1 ELSE null END) AS men_A,
COUNT(CASE WHEN nenndo = '1' and sex = '男' and hyouka = 'B' THEN 1 ELSE NULL END) AS men_B,
COUNT(CASE WHEN nenndo = '1' and sex = '男' and hyouka = 'C' THEN 1 ELSE NULL END) AS men_C,
COUNT(CASE WHEN nenndo = '1' and sex = '女' and hyouka = 'A' THEN 1 ELSE NULL END) AS women_A,
COUNT(CASE WHEN nenndo = '1' and sex = '女' and hyouka = 'B' THEN 1 ELSE NULL END) AS women_B,
COUNT(CASE WHEN nenndo = '1' and sex = '女' and hyouka = 'C' THEN 1 ELSE NULL END) AS women_C
FROM test20070707

UNION

SELECT '第二学年' AS 学年,
COUNT(CASE WHEN nenndo = '2' and sex = '男' and hyouka = 'A' THEN 1 ELSE NULL END) AS men_A,
COUNT(CASE WHEN nenndo = '2' and sex = '男' and hyouka = 'B' THEN 1 ELSE NULL END) AS men_B,
COUNT(CASE WHEN nenndo = '2' and sex = '男' and hyouka = 'C' THEN 1 ELSE NULL END) AS men_C,
COUNT(CASE WHEN nenndo = '2' and sex = '女' and hyouka = 'A' THEN 1 ELSE NULL END) AS women_A,
COUNT(CASE WHEN nenndo = '2' and sex = '女' and hyouka = 'B' THEN 1 ELSE NULL END) AS women_B,
COUNT(CASE WHEN nenndo = '2' and sex = '女' and hyouka = 'C' THEN 1 ELSE NULL END) AS women_C
FROM test20070707

UNION

SELECT '第三学年' AS 学年,
COUNT(CASE WHEN nenndo = '3' and sex = '男' and hyouka = 'A' THEN 1 ELSE NULL END) AS men_A,
COUNT(CASE WHEN nenndo = '3' and sex = '男' and hyouka = 'B' THEN 1 ELSE NULL END) AS men_B,
COUNT(CASE WHEN nenndo = '3' and sex = '男' and hyouka = 'C' THEN 1 ELSE NULL END) AS men_C,
COUNT(CASE WHEN nenndo = '3' and sex = '女' and hyouka = 'A' THEN 1 ELSE NULL END) AS women_A,
COUNT(CASE WHEN nenndo = '3' and sex = '女' and hyouka = 'B' THEN 1 ELSE NULL END) AS women_B,
COUNT(CASE WHEN nenndo = '3' and sex = '女' and hyouka = 'C' THEN 1 ELSE NULL END) AS women_C
FROM test20070707

お礼

t-kaさんありがとうございました。
参考にさせて頂きます。