行をグループ化して集約 - GROUP BY

行をグループ化して集約関数を適用する。SELECT に書ける列の制約や NULL の扱いを理解しよう

概念図

構文

sql

SELECT 列, 集約関数(...) FROM t GROUP BY 列

サンプル

部署ごとに人数と平均給与を集計する

sql

SELECT department_id, COUNT(*) AS cnt, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

GROUP BY とは

GROUP BY は指定した列の値が等しい行を 1 つのグループにまとめる句です。まとめたグループに対して COUNT / SUM / AVG などの集約関数を適用し、1 グループ 1 行の結果を返します。

「部署ごとの人数」「日付ごとの売上合計」のように、「〜ごとの」という日本語が出てきたら GROUP BY の出番です。

SELECT に書ける列の制約

標準 SQL では GROUP BY を使うとき、SELECT に書ける列は (1) GROUP BY に含めた列 か (2) 集約関数の中に入った列 のどちらかに限定されます。グループ内で値が 1 つに定まらない列をそのまま書いてはいけません。

MySQL は歴史的にこの制約が緩く、任意の列を書けてしまいました（sql_mode=ONLY_FULL_GROUP_BY を外した状態）。現在のデフォルトは厳格モードで、標準に寄せる方が安全です。

sql

-- NG: name はグループ内で一意に定まらない
SELECT department_id, name, COUNT(*)
FROM employees
GROUP BY department_id;

-- OK: 集約してまとめる
SELECT department_id, MAX(name), COUNT(*)
FROM employees
GROUP BY department_id;

NULL の扱い

GROUP BY は NULL 同士を同じグループとして扱います。等値比較では NULL ≠ NULL ですが、グループ化では例外的に一緒にまとめられます。

NULL を含む列でグループ化すると結果に NULL という「グループ」が現れるので、レポート用途では COALESCE(col, '(unknown)') で見た目を整えることが多いです。

複数列でのグループ化

複数列を並べると その組み合わせごとにグループができます。「年月 × カテゴリ」のようなマトリクス集計の基本です。

sql

SELECT order_year, category, SUM(amount)
FROM sales
GROUP BY order_year, category
ORDER BY order_year, category;

よくある落とし穴

WHERE と HAVING の混同: 集約結果への絞り込みは HAVING、元の行への絞り込みは WHERE
SELECT に素の列を書く: 標準 SQL では不可。MySQL でも ONLY_FULL_GROUP_BY 推奨
COUNT(col) と COUNT(*) の混同: NULL を数えるかどうかで結果が変わる