GROUP BYを付けたら行が減りました。集計関数の効き方と、HAVINGの使いどころを押さえます。
これは深掘りです。本線の順路ではありません。 本線のMySQLとSQLの基本の回で SELECT を覚えたあと、「合計や件数を出そうとして詰まった」人のための、寄り道の1本です。読まなくても本線は進みます。
作業場所はVMの中(Ubuntu)です。前回入れた MySQL に、appdb というデータベースがある前提で進めます。
SELECT が書けるようになると、次に必ずこれをやります。
「部署ごとの売上合計が見たい」
そして、だいたいこの3つのどれかで止まります。
- エラーが出る。
this is incompatible with sql_mode=only_full_group_byという長い文が返ってくる - 行が減る。 12行あったはずのデータが、3行になって返ってくる
- あるはずの部署が消える。 売上ゼロの部署が、結果に出てこない
3つとも、原因は同じひとつの事実です。
集計とは、複数の行を1行に潰すことである。
潰したのだから行は減りますし、潰したあとに「潰す前の列」を選べるはずがありませんし、元から行が無いものは潰しようがないので出てきません。 ここが腑に落ちると、上の3つは全部同じ話になります。
この記事では、自分で練習用のテーブルを作って、その「潰れる」ところを目で見ます。
対象レベル: 中級〜上級 / 区分: 発展
この記事でわかること
- 集計関数は
GROUP BYが無くても動くこと。そのとき必ず1行だけ返ること。ここが出発点であること COUNT(*)とCOUNT(列)は別物で、NULL があると数が食い違うこと。AVGはNULL を0として数えないことGROUP BYに書いていない列は選べないこと。そして MySQL は設定次第でエラーにならず、黙って嘘の値を返すことWHEREは潰す前、HAVINGは潰したあとのふるいで、同じ条件ならWHEREに書くほうが速いこと- 「0件」は行として出てこないこと。出したければ別の表と
LEFT JOINするしかないこと - 集計すると「どの行だったか」は失われること。それを取り戻すには別の道具が要ること
この記事でやること
- 練習用のテーブル
d_salesとd_deptを作る(NULL と、売上ゼロの部署を含む) GROUP BYを付けずにCOUNT/SUM/AVG/MIN/MAXを打ち、1行だけ返ることを見るGROUP BYに書いていない列を選んでエラーを出し、sql_modeを確認するWHEREとHAVINGを打ち分け、LEFT JOINで売上ゼロの部署を出す- 複数列でグループ化し、
WITH ROLLUPで小計を出し、月別に集計し、条件付き集計で横持ちにする - 練習用テーブルを消して後片づけする
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| 集計関数 | たくさんの行を受け取って、1つの値を返す関数。 COUNT SUM AVG MIN MAX など |
| グループ化 | 同じ値を持つ行をひとまとめにすること。まとめた1かたまりが、結果の1行になる |
| NULL | 「値が入っていない」という状態。 0でも空文字でもない。「まだ分からない」に近い |
DISTINCT | 重複を取り除く指定。COUNT(DISTINCT 列) なら「何種類あるか」になる |
sql_mode | MySQL の厳しさの設定。 どこまでを「エラー」にするかが、ここで決まる |
ONLY_FULL_GROUP_BY | その sql_mode のひとつ。あいまいな GROUP BY をエラーにする設定 |
LEFT JOIN | 2つの表をつなぐとき、左の表の行は、相手がいなくても必ず残すつなぎ方 |
WITH ROLLUP | グループ化の結果に、小計と総計の行を足してくれる指定 |
| 横持ち / 縦持ち | 縦持ち=1件1行(月ごとに行が増える)。横持ち=月が列になる形。表計算でよく見るのは横持ち |
この記事の主役は
GROUP BYではなく、集計関数のほうです。GROUP BYは「集計関数を、かたまりごとに掛ける」ための指定にすぎません。先に集計関数だけを単体で理解すると、GROUP BYは自動的に分かります。
練習用のテーブルを作る
この記事は、これだけで完結します。 前回作ったテーブルには触りません。
練習用に作るものには、すべて d_ を付けます(deep の d です)。あとで消すときに、消してよいものが一目で分かるようにするためです。
VMの中(Ubuntu)で MySQL に入ります。
入ったら、データベースを選びます。
USE appdb;
まず部署の一覧(マスタ)を作ります。 売上の表とは別に、これがあるのが後半の山場になります。
CREATE TABLE d_dept (
dept VARCHAR(20) NOT NULL PRIMARY KEY
);
INSERT INTO d_dept (dept) VALUES
('営業1課'),
('営業2課'),
('開発部'),
('総務部');
次に売上の表です。 ここに NULL の金額 を混ぜてあります。そして 総務部 の行は1件も入れません(=売上ゼロの部署)。
CREATE TABLE d_sales (
id INT AUTO_INCREMENT PRIMARY KEY,
sale_date DATE NOT NULL,
dept VARCHAR(20) NOT NULL,
staff VARCHAR(20) NOT NULL,
amount INT NULL
);
INSERT INTO d_sales (sale_date, dept, staff, amount) VALUES
('2026-06-03', '営業1課', '佐藤', 12000),
('2026-06-11', '営業1課', '佐藤', 8000),
('2026-06-18', '営業1課', '鈴木', 15000),
('2026-06-25', '営業2課', '高橋', 20000),
('2026-07-02', '営業1課', '佐藤', NULL),
('2026-07-09', '営業1課', '鈴木', 5000),
('2026-07-15', '営業2課', '高橋', 30000),
('2026-07-22', '営業2課', '田中', 10000),
('2026-08-05', '営業1課', '佐藤', 7000),
('2026-08-12', '営業2課', '田中', NULL),
('2026-08-19', '開発部', '伊藤', 3000),
('2026-08-26', '開発部', '伊藤', 9000);
入ったか確かめます。
SELECT COUNT(*) FROM d_sales;
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+
12 が返れば成功です。 違う数字なら、INSERT を打ち直す前に DELETE FROM d_sales; で空にしてからやり直してください(このテーブルは練習用なので、全消ししても何も壊れません)。
amountをINT NULLにしているのは意図的です。 「金額がまだ確定していない売上」を表しています。現実のデータには必ずこれが混ざります。 そして、この記事のつまずきの半分は NULL のせいで起きます。
[スクリーンショット: 01_テーブル作成.png] CREATE TABLE と INSERT を打ち終えて、SELECT COUNT(*) FROM d_sales; が 12 を返している画面を撮ってください。
集計関数は、行を1行に潰す
いきなり GROUP BY を書かないでください。 ここを飛ばすと、あとで必ず分からなくなります。
まず GROUP BY 無しで打つ
SELECT COUNT(*) FROM d_sales;
SELECT SUM(amount) FROM d_sales;
SELECT AVG(amount) FROM d_sales;
SELECT MIN(amount) FROM d_sales;
SELECT MAX(amount) FROM d_sales;
全部、1行しか返りません。
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+
+-------------+
| SUM(amount) |
+-------------+
| 119000 |
+-------------+
+-------------+
| AVG(amount) |
+-------------+
| 11900.0000 |
+-------------+
+-------------+
| MIN(amount) |
+-------------+
| 3000 |
+-------------+
+-------------+
| MAX(amount) |
+-------------+
| 30000 |
+-------------+
これが集計関数の正体です。
12行を受け取って、1つの値を返した。 12行は、もう手元にありません。
GROUP BY は、この「12行 → 1行」を、かたまりごとに繰り返す指定にすぎません。3つの部署でグループ化すれば、3行返ります。 行が減るのは異常ではなく、それが仕事です。
COUNT(*) と COUNT(列) と COUNT(DISTINCT 列)
ここが最初の事故です。 3つを並べて打ちます。
SELECT
COUNT(*) AS 行数,
COUNT(amount) AS 金額あり,
COUNT(DISTINCT dept) AS 部署の種類,
COUNT(DISTINCT staff) AS 担当の種類
FROM d_sales;
+--------+----------+------------+------------+
| 行数 | 金額あり | 部署の種類 | 担当の種類 |
+--------+----------+------------+------------+
| 12 | 10 | 3 | 5 |
+--------+----------+------------+------------+
同じ表を数えているのに、12 と 10 が出ました。 理由はこれだけです。
| 書き方 | 数えるもの |
|---|---|
COUNT(*) | 行の数。 中身が何であろうと、行があれば1 |
COUNT(列) | その列が NULL でない行の数。 NULL の行は数えない |
COUNT(DISTINCT 列) | その列に何種類の値があるか。 NULL は数えない |
amount が NULL の行が2つあるので、COUNT(amount) は 10 です。
「件数」と言われたとき、どちらを聞かれているのかを必ず確かめてください。 「売上は何件?」が 「登録された行数」なのか「金額が確定した件数」なのかで、答えが 12 と 10 に分かれます。SQLの間違いではなく、日本語の曖昧さが原因の事故です。
そして COUNT(DISTINCT dept) が 3 です。 部署は d_dept に4つあるのに、3 です。d_sales に行が無い 総務部 は、どうやっても数えられません。 これが後半の山場に効いてきます。
AVG は NULL を「0として」数えない
ここは、実務でいちばん静かに間違えるところです。
SELECT
SUM(amount) AS 合計,
COUNT(amount) AS 金額あり,
AVG(amount) AS 平均_NULLを無視,
SUM(amount) / COUNT(*) AS 平均_NULLを0扱い
FROM d_sales;
+--------+----------+-----------------+-------------------+
| 合計 | 金額あり | 平均_NULLを無視 | 平均_NULLを0扱い |
+--------+----------+-----------------+-------------------+
| 119000 | 10 | 11900.0000 | 9916.6667 |
+--------+----------+-----------------+-------------------+
同じデータから、2つの「平均」が出ました。
AVG(amount)は 119000 ÷ 10。NULL の行は、母数にすら入っていませんSUM(amount) / COUNT(*)は 119000 ÷ 12。NULL を0円の売上として扱ったことになります
どちらが正しいかは、SQLでは決まりません。 決めるのは意味のほうです。
| NULL の意味 | 使うべき平均 |
|---|---|
| 金額がまだ確定していない(あとで入る) | AVG(amount)。確定した分だけで平均を出す |
| 売上が発生しなかった(0円だった) | SUM(amount) / COUNT(*)。0として母数に入れる |
NULL を「0円」の意味で使ってはいけない、というのはここから来ています。0円なら 0 と入れてください。 NULL は「分からない」であって、0ではありません。
SUMも同じです。SUM(amount)は NULL を飛ばして 119000 を返しますが、全行が NULL だった場合、SUMは 0 ではなく NULL を返します。 「合計が空欄で返ってきた」ときは、たいていこれです。COALESCE(SUM(amount), 0)で 0 に寄せられます。
GROUP BY に書いていない列は選べない
ここからが本題です。 部署ごとの合計を出します。
SELECT dept, SUM(amount) AS 合計
FROM d_sales
GROUP BY dept;
+-----------+--------+
| dept | 合計 |
+-----------+--------+
| 営業1課 | 47000 |
| 営業2課 | 60000 |
| 開発部 | 12000 |
+-----------+--------+
12行が3行になりました。 これは想定どおりです。
わざとエラーを出す
では、担当者の名前も一緒に見たくなったとします。
SELECT dept, staff, SUM(amount) AS 合計
FROM d_sales
GROUP BY dept;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'appdb.d_sales.staff' which is not
functionally dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by
長いですが、言っていることは1行です。
営業1課は6行を1行に潰した。その1行のstaffは、佐藤なのか鈴木なのか?
答えられません。 潰した時点で、佐藤も鈴木も混ざって消えています。だから MySQL は「決められない」と言って止まります。
このエラーは親切です。 止めてくれているからです。
MySQL は、設定次第で黙って通してしまう
ここが MySQL 特有の、いちばん危ないところです。 まず現在の設定を見ます。
SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE, |
| ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
ONLY_FULL_GROUP_BY が入っていれば、さっきのエラーが出ます。 MySQL 8 の初期値では入っています。
問題は、これが外されている環境があることです。 昔からある本番サーバー、レンタルサーバー、古い設定ファイルを引き継いだ環境などで、外されていることがあります。外すとどうなるかを、実際に見ます。
この設定変更は「いま繋いでいる接続だけ」に効きます(SESSION を付けているため)。MySQL を出て入り直せば元に戻ります。 サーバー全体の設定は触りません。
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
もう一度、さっきのエラーになったSQLを打ちます。
SELECT dept, staff, SUM(amount) AS 合計
FROM d_sales
GROUP BY dept;
+-----------+-------+--------+
| dept | staff | 合計 |
+-----------+-------+--------+
| 営業1課 | 佐藤 | 47000 |
| 営業2課 | 高橋 | 60000 |
| 開発部 | 伊藤 | 12000 |
+-----------+-------+--------+
通ってしまいました。 そして、この結果は嘘です。
営業1課 / 佐藤 / 47000は、「佐藤が47000売った」と読めます。 実際の佐藤は27000です。47000は課全体の合計で、佐藤はグループの中から適当に1つ拾われただけです。
エラーは出ません。警告も出ません。 グラフにしても、Excelに貼っても、それらしく見えます。気づくのは、誰かが「この数字おかしくない?」と言ったときです。
設定を元に戻します。
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
あるいは、MySQL を exit して入り直してください。 それで初期値に戻ります。
新しい環境に入ったら
SELECT @@sql_mode;を1回打つ、を習慣にしてください。 同じSQLが、環境によってエラーになったり嘘を返したりするのは、ほぼこれが原因です。
[スクリーンショット: 02_only_full_group_by.png] SELECT @@sql_mode; の結果と、GROUP BY dept で staff を選んでエラー 1055 が出ている画面を、続けて撮ってください。
正しく書くと、こうなる
担当者ごとに見たいなら、担当者でグループ化します。
SELECT dept, staff, SUM(amount) AS 合計
FROM d_sales
GROUP BY dept, staff;
+-----------+-------+--------+
| dept | staff | 合計 |
+-----------+-------+--------+
| 営業1課 | 佐藤 | 27000 |
| 営業1課 | 鈴木 | 20000 |
| 営業2課 | 田中 | 10000 |
| 営業2課 | 高橋 | 50000 |
| 開発部 | 伊藤 | 12000 |
+-----------+-------+--------+
これなら 27000 です。 GROUP BY に書いた列だけが、そのまま SELECT に書ける —— という規則の意味が、ここで分かります。
並び順は保証されません。
GROUP BYは「まとめる」だけで、「並べる」とは言っていません。順番が要るならORDER BYを必ず付けてください。
HAVING と WHERE は、潰す前か、潰したあとか
両方とも「ふるい」ですが、掛けるタイミングが違います。
d_sales の全行
↓ WHERE ← 潰す前のふるい(1行ずつ見る)
残った行
↓ GROUP BY ← ここで潰れる
グループごとの1行
↓ HAVING ← 潰したあとのふるい(グループ単位で見る)
結果
HAVING にしか書けないものがあります。 「合計が5万以上の部署だけ」は、潰したあとでないと分かりません。
SELECT dept, SUM(amount) AS 合計
FROM d_sales
GROUP BY dept
HAVING SUM(amount) >= 50000;
+-----------+--------+
| dept | 合計 |
+-----------+--------+
| 営業2課 | 60000 |
+-----------+--------+
これを WHERE に書くとエラーになります。
SELECT dept, SUM(amount) AS 合計
FROM d_sales
WHERE SUM(amount) >= 50000
GROUP BY dept;
ERROR 1111 (HY000): Invalid use of group function
WHERE が動く時点では、まだ合計は存在しません。 当然です。
同じ条件は、どちらに書くと速いか
逆に、どちらにも書ける条件があります。 「7月以降だけ」を例にします。
-- A: 潰す前にふるう
SELECT dept, SUM(amount) AS 合計
FROM d_sales
WHERE sale_date >= '2026-07-01'
GROUP BY dept;
-- B: 潰したあとにふるう(この書き方はできない)
SELECT dept, SUM(amount) AS 合計
FROM d_sales
GROUP BY dept
HAVING sale_date >= '2026-07-01';
B はエラーになります(sale_date は潰されて消えているため)。が、sql_mode が緩い環境では通ってしまうことがあります。
通っても、そちらを選ぶ理由はありません。
| 読む行数 | 索引 | |
|---|---|---|
WHERE | 条件に合う行だけを読んで潰す | 使える |
HAVING | 全行を読んで潰してから捨てる | 使えない |
原則:
WHEREに書けるものはWHEREに書く。HAVINGは 「集計した結果に対する条件」専用だと思ってください。12行なら差は出ませんが、100万行だと捨てるためだけに100万行読むことになります。
「0件」は行として出てこない
ここが、集計でいちばん怖いところです。 部署ごとの件数を出します。
SELECT dept, COUNT(*) AS 件数
FROM d_sales
GROUP BY dept;
+-----------+--------+
| dept | 件数 |
+-----------+--------+
| 営業1課 | 6 |
| 営業2課 | 4 |
| 開発部 | 2 |
+-----------+--------+
3行しか出ません。 総務部 がありません。
バグではありません。 d_sales に 総務部 の行は1件もないので、潰す対象そのものが存在しません。 存在しない行は、グループにもなりません。
これは「0が出ない」という話ではなく、「行が出ない」という話です。 「売上0円の部署を洗い出したい」ときに、この結果を見て『全部署に売上がある』と読むと、間違えます。
出したければ、部署の一覧のほうから始める
d_sales には無いものを、d_sales から出すことはできません。 だから、部署が全部載っている表(d_dept)を左に置いて、そこに売上をぶら下げます。
SELECT
d.dept,
COUNT(s.id) AS 件数,
COALESCE(SUM(s.amount), 0) AS 合計
FROM d_dept d
LEFT JOIN d_sales s ON s.dept = d.dept
GROUP BY d.dept;
+-----------+--------+--------+
| dept | 件数 | 合計 |
+-----------+--------+--------+
| 営業1課 | 6 | 47000 |
| 営業2課 | 4 | 60000 |
| 開発部 | 2 | 12000 |
| 総務部 | 0 | 0 |
+-----------+--------+--------+
4行出ました。 ここで、2つの罠があります。
罠1: COUNT(*) を使うと 1 になる。
SELECT d.dept, COUNT(*) AS 件数
FROM d_dept d
LEFT JOIN d_sales s ON s.dept = d.dept
GROUP BY d.dept;
+-----------+--------+
| dept | 件数 |
+-----------+--------+
| 営業1課 | 6 |
| 営業2課 | 4 |
| 開発部 | 2 |
| 総務部 | 1 |
+-----------+--------+
総務部 が 1 になりました。 LEFT JOIN は「相手がいなくても左の行を残す」ので、中身が全部 NULL の行が1つ作られます。 COUNT(*) は行があれば数えるので、これを1と数えます。
LEFT JOIN の右側を数えるときは、必ず COUNT(右の表の列) を使ってください。 COUNT(s.id) なら NULL は数えないので 0 になります。
罠2: 条件を WHERE に書くと、0件の行が消える。
-- 7月以降だけに絞りたい(が、これは間違い)
SELECT d.dept, COUNT(s.id) AS 件数
FROM d_dept d
LEFT JOIN d_sales s ON s.dept = d.dept
WHERE s.sale_date >= '2026-07-01'
GROUP BY d.dept;
+-----------+--------+
| dept | 件数 |
+-----------+--------+
| 営業1課 | 3 |
| 営業2課 | 3 |
| 開発部 | 2 |
+-----------+--------+
総務部 がまた消えました。 LEFT JOIN が作ってくれた「中身が NULL の行」は、s.sale_date >= ... という条件で必ず落ちるからです(NULL はどんな比較でも真になりません)。WHERE を書いた瞬間に、LEFT JOIN が普通の JOIN に格下げされたのと同じ状態です。
条件は ON のほうに書きます。
SELECT d.dept, COUNT(s.id) AS 件数
FROM d_dept d
LEFT JOIN d_sales s
ON s.dept = d.dept
AND s.sale_date >= '2026-07-01'
GROUP BY d.dept;
+-----------+--------+
| dept | 件数 |
+-----------+--------+
| 営業1課 | 3 |
| 営業2課 | 3 |
| 開発部 | 2 |
| 総務部 | 0 |
+-----------+--------+
ONは「くっつける条件」、WHEREは「結果から捨てる条件」です。LEFT JOINのときだけ、この2つの差が結果に出ます。普通のJOINでは同じ結果になるので、差に気づけません。
複数列でグループ化して、小計を出す
さっきの GROUP BY dept, staff に、小計を足します。
SELECT dept, staff, SUM(amount) AS 合計
FROM d_sales
GROUP BY dept, staff WITH ROLLUP;
+-----------+--------+--------+
| dept | staff | 合計 |
+-----------+--------+--------+
| 営業1課 | 佐藤 | 27000 |
| 営業1課 | 鈴木 | 20000 |
| 営業1課 | NULL | 47000 |
| 営業2課 | 田中 | 10000 |
| 営業2課 | 高橋 | 50000 |
| 営業2課 | NULL | 60000 |
| 開発部 | 伊藤 | 12000 |
| 開発部 | NULL | 12000 |
| NULL | NULL | 119000 |
+-----------+--------+--------+
staff が NULL の行が「その部署の小計」、両方 NULL の行が「総計」です。 自分で UNION を書かなくても、1回の走査で出してくれます。
ここにも罠があります。
その NULL は「小計です」の印なのか、「データが NULL」なのか、見分けが付きません。
今回は staff が NOT NULL なので迷いませんが、NULL を許す列でやると本当に区別できません。 そのための関数が GROUPING() です。
SELECT
IF(GROUPING(dept) = 1, '【総計】', dept) AS 部署,
IF(GROUPING(staff) = 1, '【小計】', staff) AS 担当,
SUM(amount) AS 合計
FROM d_sales
GROUP BY dept, staff WITH ROLLUP;
+-----------+----------+--------+
| 部署 | 担当 | 合計 |
+-----------+----------+--------+
| 営業1課 | 佐藤 | 27000 |
| 営業1課 | 鈴木 | 20000 |
| 営業1課 | 【小計】 | 47000 |
| 営業2課 | 田中 | 10000 |
| 営業2課 | 高橋 | 50000 |
| 営業2課 | 【小計】 | 60000 |
| 開発部 | 伊藤 | 12000 |
| 開発部 | 【小計】 | 12000 |
| 【総計】 | 【小計】 | 119000 |
+-----------+----------+--------+
GROUPING(列) は、「その列が ROLLUP のせいで NULL になったなら 1」を返します。 これで区別が付きます。
GROUP BYの列の順番は、結果の意味を変えます。GROUP BY dept, staffの小計は「部署ごと」ですが、GROUP BY staff, deptにすると「担当ごと」の小計になります。WITH ROLLUPは、左の列から順に外していくからです。
日付で集計する
「月ごとの売上」は、日付をそのままグループ化しても出ません。 日付は1日単位で全部違うからです。月の形に変換してからグループ化します。
SELECT
DATE_FORMAT(sale_date, '%Y-%m') AS 年月,
COUNT(*) AS 件数,
SUM(amount) AS 合計
FROM d_sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m')
ORDER BY 年月;
+---------+--------+--------+
| 年月 | 件数 | 合計 |
+---------+--------+--------+
| 2026-06 | 4 | 55000 |
| 2026-07 | 4 | 45000 |
| 2026-08 | 4 | 19000 |
+---------+--------+--------+
これで月別が出ます。 ただし、このSQLには実務で効いてくる問題があります。
列に関数を掛けると、その列の索引が使えなくなります。
sale_date に索引が張ってあっても、DATE_FORMAT(sale_date, ...) と書いた瞬間、MySQL は全行に対して関数を計算しないと値が分からないので、索引を諦めて全部読みます。12行なら一瞬ですが、数百万行だと致命的です。
期間で絞るときは、列をそのまま比較します。
-- 索引が使える書き方
SELECT DATE_FORMAT(sale_date, '%Y-%m') AS 年月, SUM(amount) AS 合計
FROM d_sales
WHERE sale_date >= '2026-07-01' AND sale_date < '2026-09-01'
GROUP BY DATE_FORMAT(sale_date, '%Y-%m')
ORDER BY 年月;
WHERE のほうで sale_date を裸のまま比較しているのが要点です。 ここで絞ってから、残った行にだけ DATE_FORMAT が掛かります。
「索引が効いているかどうか」を目で確かめる方法は、本線の速くする前に必ず測る — パフォーマンス改善とEXPLAINでやっています。EXPLAIN を付けるだけです。この記事では、書き方の違いがあることだけ覚えて先へ進みます。
< '2026-09-01'であって<= '2026-08-31'ではないのも意図的です。sale_dateがDATETIME型になった瞬間、<= '2026-08-31'は 8月31日の00:00:00までしか拾いません(その日の日中が全部落ちます)。「翌月の1日より前」と書く癖にしておくと、型が変わっても壊れません。
条件付き集計 — 縦持ちを横持ちに変える
さっきの月別は「縦持ち」です。 月が増えると行が増えます。「部署が行、月が列」の表にしたいことがよくあります。
その道具が、集計関数の中に CASE を入れる書き方です。
SELECT
dept AS 部署,
SUM(CASE WHEN DATE_FORMAT(sale_date,'%Y-%m')='2026-06' THEN amount ELSE 0 END) AS `6月`,
SUM(CASE WHEN DATE_FORMAT(sale_date,'%Y-%m')='2026-07' THEN amount ELSE 0 END) AS `7月`,
SUM(CASE WHEN DATE_FORMAT(sale_date,'%Y-%m')='2026-08' THEN amount ELSE 0 END) AS `8月`,
SUM(amount) AS 合計
FROM d_sales
GROUP BY dept;
+-----------+-------+-------+-------+--------+
| 部署 | 6月 | 7月 | 8月 | 合計 |
+-----------+-------+-------+-------+--------+
| 営業1課 | 35000 | 5000 | 7000 | 47000 |
| 営業2課 | 20000 | 40000 | 0 | 60000 |
| 開発部 | 0 | 0 | 12000 | 12000 |
+-----------+-------+-------+-------+--------+
理屈はこれだけです。
CASEが、条件に合う行だけamountを通し、合わない行には 0 を返す。 そのうえでSUMが全部足す。
「条件で絞る」のではなく「条件に合わないものを0にして足す」という発想が、この書き方の肝です。WHERE で絞ると1つの月しか出せませんが、この書き方なら何列でも並べられます。
件数を数えるときの2つの書き方
「1万円以上の売上が何件あるか」を部署ごとに数えます。 2通りあります。
SELECT
dept AS 部署,
SUM(CASE WHEN amount >= 10000 THEN 1 ELSE 0 END) AS 高額_SUM版,
COUNT(NULLIF(amount >= 10000, 0)) AS 高額_COUNT版,
SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) AS 金額未定
FROM d_sales
GROUP BY dept;
+-----------+------------+--------------+----------+
| 部署 | 高額_SUM版 | 高額_COUNT版 | 金額未定 |
+-----------+------------+--------------+----------+
| 営業1課 | 2 | 2 | 1 |
| 営業2課 | 3 | 3 | 1 |
| 開発部 | 0 | 0 | 0 |
+-----------+------------+--------------+----------+
COUNT(NULLIF(条件, 0)) の読み方はこうです。
amount >= 10000は、MySQL では 1 / 0 / NULL を返す(amountが NULL なら比較結果も NULL)NULLIF(x, 0)は、x が 0 だったら NULL にするCOUNTは NULL を数えないので、残った 1 の数 = 条件に合った行数
短く書けますが、読み手を選びます。 迷ったら SUM(CASE ...) のほうを使ってください。 意味が字面に出ています。
ただし、両者には差が出る場面があります。
集計する行が1つも無いとき(
WHEREで全部落ちたときなど)、SUM(CASE ...)は NULL を返し、COUNT(...)は 0 を返します。
ただし、さっきの LEFT JOIN の 総務部 では、中身が NULL の行が1行できるので、高額の SUM(CASE ...) は 0 になり、逆に金額未定(amount IS NULL)が 1 と数えられます。件数を数えるつもりなら、COUNT 側か、COALESCE(SUM(...), 0) にし、LEFT JOIN では条件に s.id IS NOT NULL を足してください。
集計は「元の行」を失う
最後に、この記事のいちばん大事な限界を書きます。
SELECT dept, MAX(amount) AS 最高額
FROM d_sales
GROUP BY dept;
+-----------+--------+
| dept | 最高額 |
+-----------+--------+
| 営業1課 | 15000 |
| 営業2課 | 30000 |
| 開発部 | 9000 |
+-----------+--------+
「営業1課の最高額は15000」までは分かります。 では、それは誰の、いつの売上だったのか。
このSQLでは、絶対に分かりません。 冒頭に書いたとおりです。
集計とは、複数の行を1行に潰すこと。 潰した時点で、どの行だったかという情報は、もう手元にありません。
そして staff を SELECT に足すと、山場②のエラーに戻ります。 sql_mode を緩めて通せば、15000 と無関係な担当者の名前が並びます。ぐるっと一周して同じ場所に戻ってきます。
「最大値を持っていた行そのものを取り出す」には、集計とは別の道具が要ります。 サブクエリで一度集計してから元の表に突き合わせるか、行を潰さずに順位だけ付ける仕組みを使うかです。この記事の範囲を超えるので、ここでは「集計だけでは解けない」ことを覚えて終わりにします。
逆に言えば、これが判断の分かれ目です。
| やりたいこと | 使うもの |
|---|---|
| かたまりの数字が欲しい(合計・件数・平均) | 集計。この記事の内容で足ります |
| その数字を出した「行」が欲しい(誰が・いつ) | 集計だけでは足りません |
つまずいたところ
素直に書いた順に残します。
1つめ。エラーを消そうとして、sql_mode を変えてしまった。
検索すると「ONLY_FULL_GROUP_BY を外せば直る」という記事が大量に出てきます。外せばエラーは消えます。 消えますが、エラーが指していた「どの担当者を出すつもりなのか決まっていない」という問題は、何も解決していません。 消えたのはメッセージだけです。
外して通したSQLの結果を、そのまま表にして眺めていた時間があります。 数字が合わないことに気づいたのは、手で足し算してみたときでした。エラーメッセージは、味方でした。
2つめ。COUNT(*) で数えていて、NULL に気づかなかった。
「登録件数12件、平均11900円」と書いて、掛け算が合わないことに後から気づきました。 12 × 11900 は 142800 で、合計の 119000 になりません。COUNT(*) は12、AVG の母数は10だったからです。
平均を出したら、件数 × 平均 = 合計 を1回確かめる、という手順を挟むようにしました。合わなければ、どこかに NULL があります。
3つめ。総務部 が消えていることに、しばらく気づかなかった。
消えたものには、気づけません。 出てきた3行は全部正しいので、見ている限り何も間違っていません。「4部署あるはずなのに3行しか無い」と気づけたのは、d_dept を先に作っていたからです。
「全体が何件あるか」を別に持っておく、が対策になりました。集計結果だけを見て『これが全部だ』と判断しない。
4つめ。LEFT JOIN に WHERE を足して、また 総務部 を消した。
せっかく4行出るようにしたのに、期間で絞ろうとして WHERE s.sale_date >= ... を足した瞬間に3行に戻りました。ON に書くのだと理解するまで、JOIN の書き方を疑って時間を使いました。 原因は JOIN ではなく、NULL は比較すると真にならないという、もっと手前の規則でした。
後片づけ
練習用のテーブルを消します。 d_ で始まる2つだけです。
DROP TABLE d_sales;
DROP TABLE d_dept;
消えたか確認します。
SHOW TABLES LIKE 'd\_%';
Empty set (0.00 sec)
何も返らなければ完了です。
sql_mode を触った場合は、SET SESSION で変えただけなので接続を切れば元に戻ります。
exit
入り直して確認します。
ONLY_FULL_GROUP_BY が入っていれば、元通りです。 入っていなければ、それはこの記事の前から外れていたということなので、原因を別に探してください。
まとめ — 理解できたこと
- 集計関数は、複数の行を1行に潰す道具。
GROUP BYは、それをかたまりごとに繰り返す指定にすぎない。行が減るのは、それが仕事だから COUNT(*)は行の数、COUNT(列)は NULL でない行の数。 「件数」と言われたら、どちらを聞かれているのか確かめるAVGは NULL を母数に入れない。 0として数えたいならSUM / COUNT(*)を自分で書く。どちらが正しいかは、NULL の意味が決めるGROUP BYに無い列を選べないのは、答えが決まらないから。 MySQL はONLY_FULL_GROUP_BYが外れているとエラーも警告も出さずに嘘の値を返す。 新しい環境ではSELECT @@sql_mode;を1回打つWHEREは潰す前、HAVINGは潰したあと。 どちらにも書ける条件はWHEREに書く(読む行数が減り、索引も使える)- 0件は行として出てこない。 出したければ全部が載っている表を左に置いて
LEFT JOINする。そのときCOUNT(*)ではなくCOUNT(右の列)、条件はWHEREではなくON WITH ROLLUPの NULL は、小計の印かデータかを区別できない。GROUPING()で見分ける- 列に関数を掛けると索引が効かない。 絞り込みは列を裸のまま比較する
- 条件付き集計は「絞る」のではなく「合わないものを0にして足す」。 これで横持ちの表が作れる
- 集計すると、元の行は失われる。 「最大値を出した行そのもの」が欲しいときは、集計とは別の道具が要る
この記事で扱ったことは、全部この1行から出ています。
集計とは、行を潰すことである。
参考リンク
- MySQL 公式ドキュメント — リファレンスマニュアルの「Functions and Operators」に集計関数、「Server Administration」に SQL モードの説明があります
- roadmap.sh — SQL
- roadmap.sh — Backend Developer
- 本線の回: プロセスを止めても消えない場所 — MySQLとSQLの基本
- 本線の回: 速くする前に必ず測る — パフォーマンス改善とEXPLAIN
次に読む
この記事は 【深掘り】SQL の 7 / 17 本目です。
| 記事 | |
|---|---|
| ← 前の記事 | JOINの仕組み — 表を繋いでいない |
| → 次の記事 | SQLの組み込み関数 — CONCATにNULL1つで全部消える |
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- GROUP BY / HAVING と集計関数
- 条件付き集計と ROLLUP
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント