GROUP BYと集計関数 — 付けたら行が減った

GROUP BYと集計関数。色ごとのデータカードをまとめた束と電卓、集計結果のカード。

GROUP BYを付けたら行が減りました。集計関数の効き方と、HAVINGの使いどころを押さえます。

前回: JOINの仕組み — 表を繋いでいない

これは深掘りです。本線の順路ではありません。 本線の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 するしかないこと
  • 集計すると「どの行だったか」は失われること。それを取り戻すには別の道具が要ること

この記事でやること

  1. 練習用のテーブル d_sales と d_dept を作る(NULL と、売上ゼロの部署を含む)
  2. GROUP BY を付けずに COUNT / SUM / AVG / MIN / MAX を打ち、1行だけ返ることを見る
  3. GROUP BY に書いていない列を選んでエラーを出し、sql_mode を確認する
  4. WHERE と HAVING を打ち分け、LEFT JOIN で売上ゼロの部署を出す
  5. 複数列でグループ化し、WITH ROLLUP で小計を出し、月別に集計し、条件付き集計で横持ちにする
  6. 練習用テーブルを消して後片づけする

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
集計関数たくさんの行を受け取って、1つの値を返す関数。 COUNT SUM AVG MIN MAX など
グループ化同じ値を持つ行をひとまとめにすること。まとめた1かたまりが、結果の1行になる
NULL「値が入っていない」という状態。 0でも空文字でもない。「まだ分からない」に近い
DISTINCT重複を取り除く指定。COUNT(DISTINCT 列) なら「何種類あるか」になる
sql_modeMySQL の厳しさの設定。 どこまでを「エラー」にするかが、ここで決まる
ONLY_FULL_GROUP_BYその sql_mode のひとつ。あいまいな GROUP BY をエラーにする設定
LEFT JOIN2つの表をつなぐとき、左の表の行は、相手がいなくても必ず残すつなぎ方
WITH ROLLUPグループ化の結果に、小計と総計の行を足してくれる指定
横持ち / 縦持ち縦持ち=1件1行(月ごとに行が増える)。横持ち=月が列になる形。表計算でよく見るのは横持ち

この記事の主役は GROUP BY ではなく、集計関数のほうです。 GROUP BY は「集計関数を、かたまりごとに掛ける」ための指定にすぎません。先に集計関数だけを単体で理解すると、GROUP BY は自動的に分かります。

練習用のテーブルを作る

この記事は、これだけで完結します。 前回作ったテーブルには触りません。

練習用に作るものには、すべて d_ を付けます(deep の d です)。あとで消すときに、消してよいものが一目で分かるようにするためです。

VMの中(Ubuntu)で MySQL に入ります。

bash
$ mysql -u root -p

入ったら、データベースを選びます。

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)) の読み方はこうです。

  1. amount >= 10000 は、MySQL では 1 / 0 / NULL を返す(amount が NULL なら比較結果も NULL)
  2. NULLIF(x, 0) は、x が 0 だったら NULL にする
  3. 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

入り直して確認します。

bash
$ mysql -u root -p -e "SELECT @@sql_mode;"

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行から出ています。

集計とは、行を潰すことである。

参考リンク


次に読む

この記事は 【深掘り】SQL の 7 / 17 本目です。

記事
← 前の記事JOINの仕組み — 表を繋いでいない
→ 次の記事SQLの組み込み関数 — CONCATにNULL1つで全部消える

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Aggregate Queries
  • GROUP BY / HAVING と集計関数
  • 条件付き集計と ROLLUP

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次