※この記事にはアフィリエイト広告(プロモーション)が含まれます。
この記事の出力は MySQL 8.4.11(Ubuntu 26.04)で採りました。
まず見る(結論)
| 出したいもの | 直し方 |
|---|---|
| 列の組み合わせごとに集計したい | その列も GROUP BY に足す |
| グループ内で値が1つに決まる列(部署名など) | 主キーで GROUP BY するか、ANY_VALUE() で囲む |
| グループ内で最大・最新の行の別の列 | ROW_NUMBER() か、MAX を取った副問い合わせと結合する |
sql_mode から ONLY_FULL_GROUP_BY を外すとエラーは消えますが、返る値はどの行のものか決まりません(後述)。
エラーの中身
SELECT dept_id, name, COUNT(*) FROM emp GROUP BY dept_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fsv.emp.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Expression #2 は SELECT の2番目の列、'fsv.emp.name' は原因の列を示します。部署 1 には佐藤と鈴木の2人がいるので、name にどちらを出すか決められず、エラーになります。
ONLY_FULL_GROUP_BY は既定の sql_mode に入っています。
SELECT @@sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
同じ原因で出る別の番号
| SQL | エラー |
|---|---|
SELECT name, COUNT(*) FROM emp(GROUP BY なし) |
ERROR 1140 In aggregated query without GROUP BY |
... GROUP BY dept_id ORDER BY salary |
ERROR 1055 Expression #1 of ORDER BY clause |
... GROUP BY dept_id HAVING salary > 300 |
ERROR 1054 Unknown column 'salary' in 'having clause' |
SELECT DISTINCT dept_id FROM emp ORDER BY salary |
ERROR 3065 … this is incompatible with DISTINCT |
HAVING では 1055 ではなく 1054 になるので、列名の打ち間違いと見分けがつきません。列が存在するのに 1054 が出たら、集計していない列を HAVING に書いていないかを確かめます(ERROR 1054 の記事)。
直し方
GROUP BY に足す
GROUP BY dept_id, name にすると通ります。ただし集計の単位が「部署と名前の組」に変わり、件数はすべて 1 になりました。部署ごとの件数が欲しいなら、この直し方は使えません。
主キーでまとめる
GROUP BY に主キーを書けば、同じ表の他の列は値が1つに決まるので SELECT に出せます。結合した表でも同じです。
SELECT d.id, d.dname, COUNT(*) FROM emp e JOIN dept d ON d.id = e.dept_id GROUP BY d.id;
+----+--------+----------+
| id | dname | COUNT(*) |
+----+--------+----------+
| 1 | 営業 | 2 |
| 2 | 開発 | 3 |
+----+--------+----------+
GROUP BY e.dept_id(結合条件で d.id と等しい列)でも d.dname を出せました。
ANY_VALUE で囲む
SELECT dept_id, ANY_VALUE(name) AS name, COUNT(*) FROM emp GROUP BY dept_id;
+---------+--------+----------+
| dept_id | name | COUNT(*) |
+---------+--------+----------+
| 1 | 佐藤 | 2 |
| 2 | 高橋 | 3 |
+---------+--------+----------+
「グループ内のどれか1つでよい」という指定で、どの行が選ばれるかは保証されません。グループ内で値がすべて同じ列に使います。
各グループで最大の行を取る
部署ごとに給与が最高の人を出したいときに SELECT dept_id, name, MAX(salary) ... GROUP BY dept_id と書くと 1055 になります。行ごと取り出す書き方にします。
SELECT dept_id, name, salary
FROM (SELECT dept_id, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM emp) t
WHERE rn = 1;
+---------+--------+--------+
| dept_id | name | salary |
+---------+--------+--------+
| 1 | 鈴木 | 350 |
| 2 | 伊藤 | 420 |
+---------+--------+--------+
部署ごとの MAX(salary) を副問い合わせで出して dept_id と salary で結合しても同じ結果でした。こちらは最高額が同じ人が2人いれば両方出ます。
ONLY_FULL_GROUP_BY を外すと
SET SESSION sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');
SELECT dept_id, name, MAX(salary) FROM emp GROUP BY dept_id;
+---------+--------+-------------+
| dept_id | name | MAX(salary) |
+---------+--------+-------------+
| 1 | 佐藤 | 350 |
| 2 | 高橋 | 420 |
+---------+--------+-------------+
エラーは出なくなりました。しかし、給与 350 は鈴木、420 は伊藤なのに、name には別の人が出ています。
古いアプリを動かすために外す場合、SET SESSION はその接続だけに効き、SET GLOBAL は再起動で元に戻ります。恒久的に外すには設定ファイルの [mysqld] に sql_mode を書きます。ほかのモード(STRICT_TRANS_TABLES など)まで消さないよう、今の値から ONLY_FULL_GROUP_BY だけを除いた文字列を書きます。
関連: MySQLのERROR 1054 / MySQLのGROUP_CONCATが途中で切れる / MySQLのEXPLAINの見方 / MySQLのERROR 1064