「地域別の売上一覧に、小計と総計も付けてほしい」。集計レポートでよくある依頼です。GROUP BYにWITH ROLLUPを付けると、明細・小計・総計を1本のクエリでまとめて出せます。この記事では、売上テーブルを例にWITH ROLLUPの基本を説明します。続いて、GROUPING()関数で小計行に「小計」「総計」のラベルを付ける方法、小計だけを取り出す方法を紹介します。後半では、元データのNULLと見分けがつかなくなる問題など、つまずきやすい点もまとめました。
このシナリオで考える
地域とカテゴリごとの売上を記録した sales テーブルを使います。データはわずか6行です。小計や総計を手で計算して確かめられるように、あえて少なくしてあります。
CREATE TABLE sales (
id INT PRIMARY KEY AUTO_INCREMENT,
region VARCHAR(20),
category VARCHAR(20) NOT NULL,
amount INT NOT NULL
);
INSERT INTO sales (region, category, amount) VALUES
('東日本', '食品', 1200),
('東日本', '食品', 800),
('東日本', '雑貨', 500),
('西日本', '食品', 900),
('西日本', '雑貨', 700),
('西日本', '雑貨', 300);region はNULLを入れられる定義にしています。この点は後半の「よくある落とし穴」で使います。
WITH ROLLUPの基本：総計行を1行足す
WITH ROLLUPは、GROUP BYの結果に「1つ上の階層の合計行」を足す修飾子です。まずは地域ごとの合計に、総計を付けてみます。
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region WITH ROLLUP;+--------+-------+
| region | total |
+--------+-------+
| 東日本 | 2500 |
| 西日本 | 1900 |
| NULL | 4400 |
+--------+-------+最後の行が、WITH ROLLUPで足された総計行です。この行では、グループ分けに使った列（ここでは region）がNULLになります。「すべての地域をまとめた」という意味のNULLと覚えてください。
SUMだけでなく、COUNTやAVGも総計行で計算されます。ただしAVGの総計行は「地域ごとの平均の平均」ではありません。元の6行すべての平均（4400 ÷ 6 ≒ 733.3）になる点に気をつけましょう。
列を2つ並べると小計と総計が出る
GROUP BYに列を2つ書くと、WITH ROLLUPは小計と総計の両方を足します。地域とカテゴリで集計してみましょう。
SELECT region, category,
SUM(amount) AS total,
COUNT(*) AS cnt
FROM sales
GROUP BY region, category WITH ROLLUP;+--------+----------+-------+-----+
| region | category | total | cnt |
+--------+----------+-------+-----+
| 東日本 | 雑貨 | 500 | 1 |
| 東日本 | 食品 | 2000 | 2 |
| 東日本 | NULL | 2500 | 3 |
| 西日本 | 雑貨 | 1000 | 2 |
| 西日本 | 食品 | 900 | 1 |
| 西日本 | NULL | 1900 | 3 |
| NULL | NULL | 4400 | 6 |
+--------+----------+-------+-----+category だけがNULLの行は地域ごとの小計、両方がNULLの行は総計です。出てくる行の種類を表にまとめます。
| 行の種類 | region | category | 集計の範囲 |
|---|---|---|---|
| 明細 | 値あり | 値あり | 地域×カテゴリ |
| 小計 | 値あり | NULL | 地域ごと |
| 総計 | NULL | NULL | 全体 |
小計は、右側の列から順に外して作られます。そのため、GROUP BYに書く列の順番が結果を左右します。GROUP BY category, region WITH ROLLUP と書けば、カテゴリごとの小計に変わります。「大きい区分を左、細かい区分を右」と覚えておくと迷いません。
GROUPING()で小計行にラベルを付ける
NULLのままでは、報告資料として読みにくいでしょう。MySQL 8.0では、GROUPING()関数で小計・総計の行を見分けられます。WITH ROLLUPによってNULLになった列なら1、それ以外なら0を返す関数です。
SELECT
CASE WHEN GROUPING(region) = 1 THEN '総計'
ELSE region END AS region_label,
CASE
WHEN GROUPING(region) = 1 THEN ''
WHEN GROUPING(category) = 1 THEN '小計'
ELSE category
END AS category_label,
SUM(amount) AS total
FROM sales
GROUP BY region, category WITH ROLLUP;+--------------+----------------+-------+
| region_label | category_label | total |
+--------------+----------------+-------+
| 東日本 | 雑貨 | 500 |
| 東日本 | 食品 | 2000 |
| 東日本 | 小計 | 2500 |
| 西日本 | 雑貨 | 1000 |
| 西日本 | 食品 | 900 |
| 西日本 | 小計 | 1900 |
| 総計 | | 4400 |
+--------------+----------------+-------+そのまま表計算ソフトに貼れる形になりました。列が1つだけなら、IF(GROUPING(region), '総計', region) のように短く書くこともできます。別名を元の列名と変えているのは、読み手が「加工後の列」だと分かるようにするためです。
小計だけを取り出す・並び順を変える
GROUPING()はHAVING句でも使えます。明細は要らず、小計と総計だけ欲しい場面で便利です。
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region, category WITH ROLLUP
HAVING GROUPING(category) = 1;+--------+-------+
| region | total |
+--------+-------+
| 東日本 | 2500 |
| 西日本 | 1900 |
| NULL | 4400 |
+--------+-------+GROUPING()に複数の列を渡すと、各列の判定結果をビットとして組み合わせた数を返します。右端の引数がいちばん下の桁です。
| 行の種類 | GROUPING(region) | GROUPING(category) | GROUPING(region, category) |
|---|---|---|---|
| 明細 | 0 | 0 | 0 |
| 小計 | 0 | 1 | 1 |
| 総計 | 1 | 1 | 3 |
並び順も変えられます。MySQL 8.0.12以降では、WITH ROLLUPとORDER BYを一緒に使えるようになりました。次の例では、総計を先頭に置き、各地域の小計を明細の上に出しています。
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY region, category WITH ROLLUP
ORDER BY GROUPING(region) DESC, region,
GROUPING(category) DESC, category;+--------+----------+-------+
| region | category | total |
+--------+----------+-------+
| NULL | NULL | 4400 |
| 東日本 | NULL | 2500 |
| 東日本 | 雑貨 | 500 |
| 東日本 | 食品 | 2000 |
| 西日本 | NULL | 1900 |
| 西日本 | 雑貨 | 1000 |
| 西日本 | 食品 | 900 |
+--------+----------+-------+8.0.11以前では、この書き方はエラーになります。その場合はWITH ROLLUPのクエリを派生テーブル（FROM句のサブクエリ）で包み、外側で並べ替えてください。
よくある落とし穴
いちばん多いのは、元データのNULLと総計行のNULLが区別できなくなる問題です。地域が未入力の売上を1行足してみます。
INSERT INTO sales (region, category, amount)
VALUES (NULL, '食品', 400);
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region WITH ROLLUP;+--------+-------+
| region | total |
+--------+-------+
| NULL | 400 |
| 東日本 | 2500 |
| 西日本 | 1900 |
| NULL | 4800 |
+--------+-------+NULLの行が2つあり、どちらが総計か見た目では分かりません。GROUPING()で総計を判定してから、残ったNULLをCOALESCEで置き換えるのが確実です。
SELECT
CASE
WHEN GROUPING(region) = 1 THEN '総計'
ELSE COALESCE(region, '未設定')
END AS region_label,
SUM(amount) AS total
FROM sales
GROUP BY region WITH ROLLUP;+--------------+-------+
| region_label | total |
+--------------+-------+
| 未設定 | 400 |
| 東日本 | 2500 |
| 西日本 | 1900 |
| 総計 | 4800 |
+--------------+-------+COALESCEだけでラベルを付けると、総計行まで「未設定」になってしまいます。判定の順番は「GROUPING()が先、COALESCEが後」です。ほかにも、次のような点でつまずきがちです。
| 症状 | 原因 | 対処 |
|---|---|---|
WHERE region IS NULL で総計行が取れない |
小計・総計のNULLは集計の最後に付くため、WHEREの時点では存在しない | HAVINGとGROUPING()で絞る |
| LIMITで件数が合わない | LIMITはWITH ROLLUPの後に効き、小計行も1件と数える | 件数を絞るなら、派生テーブルで明細を先に絞る |
| GROUP BYにない列でエラーになる | ONLY_FULL_GROUP_BYが有効（MySQL 8.0の既定） | GROUP BYに含めるか、集約関数かANY_VALUE()で包む |
| ORDER BYやDISTINCTを付けるとエラー | MySQL 8.0.11以前の制限 | 8.0.12以降に上げるか、派生テーブルで包む |
方言の違いにも触れておきます。PostgreSQLやSQL Serverでは、標準SQLの GROUP BY ROLLUP(region, category) という書き方を使います。CUBEやGROUPING SETSといった、より柔軟な集計も可能です。MySQLでは WITH ROLLUP の形で書くと覚えておきましょう。
まとめ
- GROUP BYにWITH ROLLUPを付けると、小計・総計の行が足される
- 小計は右の列から順に外して作られるので、列は「大きい区分を左」に書く
- GROUPING()で小計・総計の行を判定し、ラベル付けやHAVINGでの絞り込みに使う
- 元データのNULLとは、GROUPING()とCOALESCEを組み合わせて見分ける
集計の基本に不安があれば、GROUP BYとHAVINGの記事もあわせて読んでみてください。
参考リンク
- MySQL 8.0 リファレンス: GROUP BY Modifiers
- MySQL 8.0 リファレンス: Miscellaneous Functions（GROUPING）
- PostgreSQL ドキュメント: GROUPING SETS, CUBE, and ROLLUP
- GROUP BYとHAVINGの違いとWHERE句との使い分け（当サイト）
アイキャッチ画像: Photo by Team Nocoloco on Unsplash
