CASE式とHAVING句の応用問題をといてみる

達人に学ぶSQLの第六章にある問題をといてみます。
なんとなく記事にしてみました。

データ投入

CREATE TABLE TestResults 
(student_id INT, class VARCHAR(1), sex VARCHAR(4), score INT)

INSERT INTO TestResults VALUES (001, 'A', '男', 100);
INSERT INTO TestResults VALUES (002, 'A', '女', 100);
INSERT INTO TestResults VALUES (003, 'A', '女', 49);
INSERT INTO TestResults VALUES (004, 'A', '男', 30);
INSERT INTO TestResults VALUES (005, 'B', '女', 100);
INSERT INTO TestResults VALUES (006, 'B', '男', 92);
INSERT INTO TestResults VALUES (007, 'B', '男', 80);
INSERT INTO TestResults VALUES (008, 'B', '男', 80);
INSERT INTO TestResults VALUES (009, 'B', '女', 10);
INSERT INTO TestResults VALUES (010, 'C', '男', 92);
INSERT INTO TestResults VALUES (011, 'C', '男', 80);
INSERT INTO TestResults VALUES (012, 'C', '女', 21);
INSERT INTO TestResults VALUES (013, 'D', '女', 100);
INSERT INTO TestResults VALUES (014, 'D', '女', 0);
INSERT INTO TestResults VALUES (015, 'D', '女', 0);

SELECT * FROM TestResults;
+------------+-------+------+-------+
| student_id | class | sex  | score |
+------------+-------+------+-------+
|          1 | A     | 男   |   100 |
|          2 | A     | 女   |   100 |
|          3 | A     | 女   |    49 |
|          4 | A     | 男   |    30 |
|          5 | B     | 女   |   100 |
|          6 | B     | 男   |    92 |
|          7 | B     | 男   |    80 |
|          8 | B     | 男   |    80 |
|          9 | B     | 女   |    10 |
|         10 | C     | 男   |    92 |
|         11 | C     | 男   |    80 |
|         12 | C     | 女   |    21 |
|         13 | D     | 女   |   100 |
|         14 | D     | 女   |     0 |
|         15 | D     | 女   |     0 |
+------------+-------+------+-------+

生徒のテスト結果を保持するテーブルです。

問題

第１問 クラスの75%以上の生徒が80点以上のクラスを選択せよ。

自分の回答

SELECT class FROM TestResults
GROUP BY class
HAVING COUNT(*) * 0.75 <= SUM(CASE WHEN score >= 80 THEN 1
                              ELSE 0 END);

+-------+
| class |
+-------+
| B     |
+-------+

解説

classごとにグループ分けして、COUNT(*)でグループごとの行の数を求めています。
75%なので、0.75をかけています。
式の反対側では、80点以上の生徒にフラグをつけて、SUMで合計しています。

結果

正解でした。

---

第2問　５０点以上をとった生徒のうち、男子の数が女子の数より多いクラスを選択せよ。

自分の回答

SELECT class FROM TestResults
GROUP BY class
HAVING SUM(CASE WHEN sex = '男' AND score >= 50 THEN 1 ELSE 0 END) >
      SUM(CASE WHEN sex = '女' AND score >= 50 THEN 1 ELSE 0 END);

なぜかエラーになり実行できず。

書籍の回答

SELECT class FROM TestResults
GROUP BY class
HAVING SUM(CASE WHEN score >= 50 AND sex = '男' THEN 1 ELSE 0 END) >
      SUM(CASE WHEN score >= 50 AND sex = '女' THEN 1 ELSE 0 END);

+-------+
| class |
+-------+
| B     |
| C     |
+-------+

条件逆にしただけでエラーがなくなりました。どういうことでしょうか......

解説

一問目でやってることを両辺に増やしただけです。

---

第３問　女子の平均点が、男子の平均点よりも高いクラスを選択せよ。

自分の回答

SELECT class FROM TestResults
GROUP BY class
HAVING AVG(CASE WHEN sex = '女' THEN score ELSE NULL END) >
       AVG(CASE WHEN sex = '男' THEN score ELSE NULL END);

+-------+
| class |
+-------+
| A     |
+-------+

結果

正解！
NULLが悪影響を与えるかもと思いましたが、これで正しかったようです。

解説

NULLのところを0にしてしまうと、条件と逆の性別の時に平均点を下げてしまうのを意識しました。

感想

パズルを組み立てているようで面白かったです。
２問目がエラーになった原因がよく分からないので、分かる方コメントお願いします。