部署ごとの売上合計を出すSQLに、担当者名も表示したい。そこで列を足したら、Expression #2 of SELECT list is not in GROUP BY clauseと怒られた。ONLY_FULL_GROUP_BYのエラーでは、まず集計後の1行に、どの担当者を載せたいのかを決めます。
部署の合計だけなら担当者列を外します。担当者ごとの合計が必要なら、部署と担当者の両方で集計します。最新の担当者を載せたいなら、最新行を選ぶ処理が必要です。列をGROUP BYへ追加するだけでは、元の「部署につき1行」という意味が変わることがあります。
ONLY_FULL_GROUP_BYは、集計後に決められない列を指摘している
次のSQLで、接続しているMySQLのバージョンと現在の設定を確認できます。アプリでだけ失敗する場合は、アプリと同じ接続条件でも確認します。
SELECT VERSION(), @@SESSION.sql_mode, @@GLOBAL.sql_mode;
ONLY_FULL_GROUP_BYが有効なとき、集計単位から値が決まらない非集約列は拒否されます。GROUP BYに書かれた列や、その列から一意に決まる列、SUMなどで集約した結果は、それぞれ値の決め方を説明できます。
この設定はMySQL 8で初めて有効になったわけではありません。MySQL 5.7.5の変更記録でも既定で有効化されています。「以前は動いた」という場合は、バージョン名だけでなく、旧環境と新環境のSESSION設定も比較します。
ここでは、検証用DBで次の一時テーブルを作ります。本番の売上テーブルは使いません。一時テーブルは現在の接続だけに存在するため、以降のSQLも同じ接続で実行してください。
-- Use a disposable database and keep this connection open.
CREATE TEMPORARY TABLE groupby_sales (
id INT PRIMARY KEY,
department VARCHAR(20) NOT NULL,
salesperson VARCHAR(20) NOT NULL,
amount INT NOT NULL,
sold_at DATETIME NOT NULL
);
INSERT INTO groupby_sales VALUES
(1, 'East', 'Aki', 100, '2026-09-01 10:00:00'),
(2, 'East', 'Beni', 200, '2026-09-02 10:00:00'),
(3, 'East', 'Chika', 50, '2026-09-02 10:00:00'),
(4, 'West', 'Daichi', 400, '2026-09-01 12:00:00');
EastにはAkiの100、Beniの200、Chikaの50があり、部署合計は350です。最新日時はBeniとChikaが同じです。どちらを最新の1件にするかまで、後ほど条件で決めます。
SELECT department, salesperson, SUM(amount) AS total
FROM groupby_sales
GROUP BY department;
このSQLは検証環境のMySQL 8.4.11でエラー1055になりました。Eastの350に添えるsalespersonが、Aki・Beni・Chikaのどれか決まっていないためです。エラーのExpression #2は、このSELECTでは2番目の式であるsalespersonを指しています。
部署の合計か、担当者の合計かで修正が変わる
| 欲しい結果 | 修正 | Eastの結果 |
|---|---|---|
| 部署につき1行の合計 | 担当者列をSELECTから外す | 350の1行 |
| 部署・担当者につき1行の合計 | 両方をGROUP BYに書く | Aki 100、Beni 200、Chika 50の3行 |
| 部署合計と最新担当者 | 合計と最新行の選択を別々に定義する | 後述の条件ならChika、350の1行 |
部署ごとの合計だけなら、曖昧な列を外す
SELECT department, SUM(amount) AS total
FROM groupby_sales
GROUP BY department
ORDER BY department;
結果はEastが350、Westが400の2行です。画面やCSVに部署合計を載せる目的なら、この形で足ります。担当者を使わない処理に、エラー回避用のダミーの値を付ける必要はありません。
担当者別にも分けたいなら、GROUP BYへ追加する
SELECT department, salesperson, SUM(amount) AS total
FROM groupby_sales
GROUP BY department, salesperson
ORDER BY department, salesperson;
こちらはEastが3行、Westが1行になります。構文上は成功しますが、「部署の件数」を数える後続処理や、1部署1行を想定する一覧には、そのまま渡せません。列を追加したあとも件数と合計を確認し、どの単位を返すSQLなのかを名前や呼び出し元とそろえます。
最新の担当者が必要なら、日時と同順位の決め方を指定する
MAX(sold_at)は最新の日時を返しますが、横に書いた担当者名を、その日時の行から自動で持ってくる機能ではありません。最新の行を選びたい場合は、MySQL 8系のROW_NUMBER()を使うと、選ぶ条件を書き分けられます。
WITH ranked AS (
SELECT department, salesperson, sold_at,
SUM(amount) OVER (PARTITION BY department) AS total,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY sold_at DESC, id DESC
) AS row_num
FROM groupby_sales
)
SELECT department, salesperson AS latest_salesperson, sold_at, total
FROM ranked
WHERE row_num = 1
ORDER BY department;
内側では、部署全体の合計をSUM(amount) OVER (PARTITION BY department)で各行に付けます。同時に、部署の中で売上日時の新しい順、同じ日時ならidの大きい順に番号を付けます。外側が1番の行だけを残すので、その担当者名と部署全体の合計が同じ出力行に並びます。
結果はEastがChika・350、WestがDaichi・400です。BeniとChikaは日時が同じなので、この例ではidが大きいChikaを選びました。idの大小を同順位の決着に使ってよいかは、実際の仕様で決めます。同時刻の全担当者を出す必要があるなら、「必ず1件」の要件から見直します。
ROW_NUMBERの公式説明でも、行番号はウィンドウ内のORDER BYで決まります。日時だけで同じ値がある場合まで一意に決まったと考えず、必要な順序を最後まで書くのが要点です。
ANY_VALUEは「どれでもよい」を許す。最新を選ぶ指定ではない
SELECT department, ANY_VALUE(salesperson) AS any_salesperson,
SUM(amount) AS total, MAX(sold_at) AS latest_sold_at
FROM groupby_sales
GROUP BY department
ORDER BY department;
このSQLは、部署合計と最新日時に加えて、任意の担当者を表示します。any_salespersonがlatest_sold_atの行の担当者である保証はありません。試した結果がたまたま期待と同じでも、最新担当者を取るSQLの代わりにはしません。
ANY_VALUEを使えるのは、どの値が選ばれても画面や処理の意味が変わらない場合です。例えば「参考としてグループ内の値を一つ出す」と明示できるかを確認します。注文者、最新状態、請求先など、選ばれる値で処理が変わる列を包んでエラーだけ消すと、誤った行を使う問題が残ります。
GROUP BYの外側にORDER BYを足しても、グループの中から選ばれる値は指定できません。MySQLのGROUP BYの説明では、値の選択後に結果が並べ替えられることが示されています。先頭や末尾の担当者が必要なら、前節のように選択条件そのものをSQLにします。
主キーから名前が決まる場合は、機能従属で許可される
すべての非集約列をGROUP BYへ並べないといけない、というわけでもありません。次は部署マスターの主キーを基準に集計します。
CREATE TEMPORARY TABLE groupby_departments (
department VARCHAR(20) PRIMARY KEY,
department_name VARCHAR(30) NOT NULL
);
INSERT INTO groupby_departments VALUES
('East', 'East team'), ('West', 'West team');
SELECT d.department, d.department_name, SUM(s.amount) AS total
FROM groupby_departments AS d
JOIN groupby_sales AS s ON s.department = d.department
GROUP BY d.department
ORDER BY d.department;
この例ではd.departmentが主キーなので、部署名は一意に決まります。MySQL 8.4.11では、GROUP BYに部署名を追加せず、East team・350、West team・400を取得できました。これが列の「機能従属」を利用できる一例です。
単に現在のデータで同じ名前が並んでいるだけの状態と、主キーなどの制約で決まる状態は分けます。複数のJOINや式を含むSQLでは、同じように推論されるとは限りません。エラーになった列について、どのキーから決まるのか、結合で行が増えていないかを確認します。
sql_modeを変える前に、返る値を仕様として説明できるか確認する
既存アプリを調査していて、設定差が原因かを切り分けたいこともあります。その場合も、検証用の接続でONLY_FULL_GROUP_BYだけを一時的に外し、元へ戻します。次は同じ接続で続けて実行する確認例です。
-- Run only in a disposable test connection.
SET @saved_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = sys.list_drop(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY');
SELECT department, salesperson, SUM(amount) AS total
FROM groupby_sales
GROUP BY department;
SET SESSION sql_mode = @saved_sql_mode;
SELECT @@SESSION.sql_mode = @saved_sql_mode AS restored;
この例ではエラーだったSQLが実行でき、最後のrestoredは1になりました。ただし、実行できることは担当者の選択が正しい証拠ではありません。確認途中で止めた場合は、復元のSET文まで実行するか、検証用接続を閉じます。
SET SESSIONの変更は今の接続が対象です。アプリの別接続や、既存のコネクションプールが同じ設定になるわけではありません。SET GLOBALは新しい接続への既定値を変える操作なので、今回の一つのSQLを通すために全体へ広げないようにします。スコープの確認方法は、MySQLのシステム変数の確認・変更でも説明しています。
sql_mode = ''で全設定を空にすると、集計以外の検査も変わります。復元値を控えずに設定を上書きするのではなく、対象SQLと、その呼び出し元が期待する結果を先に照合します。
修正を終えるときは、SQLが成功したかに加え、返る行数、集計の総額、同時刻の扱いを確認します。今回なら「Eastは1行で350か」「最新を求める画面ではChikaが選ばれるか」を別々に確かめられます。エラーを消すことと、必要な答えを返すことを同じ確認で済ませないのが、再発を防ぐための一歩です。