GROUP BY と HAVING — グループ集計

本記事で使うデータ — score テーブル

前の記事ではテーブル全体を 1 つの値にまとめました。本記事の `GROUP BY`（グループ化。同じ値を持つ行を 1 つのグループにまとめる句）は、集計を グループ単位 で行い、グループの数だけ集計行を返します。さらに `HAVING` で「平均が 80 以上のグループだけ」のように 集計結果を絞り込み ます。

題材は前回と同じ score テーブル（30 行 = 10 名 × 3 科目）。name で 10 グループ、subject で 3 グループになります。

演習に入る前に、score テーブルの 列定義 と データのサンプル を確認しておきます。

① PRAGMA table_info(score); で列名・型・主キーを確認してください。

② SELECT * FROM score LIMIT 5; で先頭 5 行のデータをプレビューしてください。

GROUP BY — グループごとに集計する

GROUP BY 列 を書くと、その列の値が同じ行が 1 つのグループにまとめられ、集計関数は グループごとに 1 回ずつ 計算されます。SELECT name, AVG(score) FROM score GROUP BY name; なら、氏名が同じ 3 行（3 科目分）が 1 グループになり、氏名ごとに 1 行ずつ平均が返ります。SELECT に書ける「素の列」は `GROUP BY` に指定した列 だけで、それ以外の列は集計関数で包む必要があります（グループ内で値が 1 つに定まらないため）。

-- 科目ごとの 件数 / 平均 / 最高点 (subject で 3 グループ)
SELECT
  subject,
  COUNT(*)             AS row_count,
  ROUND(AVG(score), 1) AS avg_score,
  MAX(score)           AS top_score
FROM score
GROUP BY subject;

「生徒一人ひとりの平均点を一覧にしたい」という要件を想定します。（正しく実行できれば解説が表示されます）

① score テーブルを 氏名ごと にグループ化してください。

② グループごとに、氏名を name、受験科目数を subjects、平均点を avg_score という別名で、この順に取り出してください。

③ 平均は小数第 2 位までに丸めてください。並び順は指定しません。

HAVING — 集計結果を絞り込む

グループ集計の後で「平均が 80 以上のグループだけ見たい」のように 集計値で絞り込みたい ことがあります。これは WHERE ではできません。WHERE はグループ化の前に個々の行を判定する句なので、AVG(score) のようなグループ集計値をまだ計算していないからです。そこで使うのが `HAVING` です。HAVING は GROUP BY の後ろに書き、グループ集計の結果に対して 条件を判定します。HAVING AVG(score) >= 80 と書けば、平均 80 以上のグループだけが残ります。

-- 平均が 85 を超える氏名だけ (HAVING で集計値を判定)
SELECT
  name,
  ROUND(AVG(score), 2) AS avg_score
FROM score
GROUP BY name
HAVING AVG(score) > 85;

「平均点が 80 以上の優秀者だけをリストアップしたい」という要件を想定します。

① score テーブルを氏名ごとにグループ化してください。

② グループごとに、氏名を name、平均点を avg_score という別名（小数第 2 位まで）で取り出してください。

③ 平均点が 80 以上 のグループだけに絞ってください。並び順は指定しません。

WHERE と HAVING の違い — 行を絞るか、グループを絞るか

WHERE と HAVING はどちらも絞り込みですが、働くタイミングが違います。WHERE は グループ化する前 に、個々の行を判定して落とします。HAVING は グループ化して集計した後 に、グループ単位で判定して落とします。実行順序は FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY です。

両方を同時に使うこともでき、その場合は「WHERE で対象行を絞ってからグループ化し、HAVING で集計結果を絞る」という流れになります。たとえば「Math と English だけを対象に、氏名ごとの平均が 80 以上の人」を出すなら、WHERE subject IN ('Math','English') で行を絞り、HAVING AVG(score) >= 80 でグループを絞ります。

-- WHERE と HAVING を併用する
-- Science を除外し、平均が 80 以上の氏名だけ残す
SELECT
  name,
  ROUND(AVG(score), 2) AS avg_two
FROM score
WHERE subject <> 'Science'
GROUP BY name
HAVING AVG(score) >= 80;

「Math と English の 2 科目だけを対象に、氏名ごとの平均が 85 以上の人を出したい」という要件を想定します。

① score テーブルから、subject が Math または English の行だけに絞ってください（Science は対象外）。

② 残った行を氏名ごとにグループ化し、氏名を name、2 科目の平均点を avg_two（小数第 2 位まで）で取り出してください。

③ さらに 平均が 85 以上 のグループだけに絞ってください。並び順は指定しません。

「科目ごとの平均点を、高い順のランキングで表示したい」という要件を想定します。

① score テーブルを 科目ごと にグループ化してください。

② グループごとに、科目を subject、受験人数を students、平均点を avg_score（小数第 2 位まで）で、この順に取り出してください。

③ 平均点の高い順 に並べてください。