SQL Server の ROLLUP / GROUPING SETS で帳票の小計・総計行を出す

みなさんこんにちは！ヒロポンです！

帳票の SQL でこういう注文、来ませんか？？

「部署ごとの合計と、全体の総計を、同じ一覧に出しといて」。

明細があって、部署ごとの小計があって、いちばん下に総計。Excel なら小計機能でポチポチやるやつです。SQL でやろうとすると、最初はだいたい GROUP BY の結果に UNION ALL で総計行を自前で足す、みたいな書き方になる。俺も昔そうしてました。

でも SQL Server には、GROUP BY に小計・総計を足すための専用構文がある。それが ROLLUP と GROUPING SETS です。

今回はこの2つがそもそも何を解決するのか、そして「小計行の集計キーが NULL になる」という最初に引っかかる点を GROUPING() でどう捌くのか。Docker の SQL Server で実際に動かしながら積み上げていきます。

忙しい人向けに最初にまとめ

• GROUP BY ROLLUP(部署, 商品) と書くと、明細に加えて部署ごとの小計と全体の総計が自動で足される
• 小計・総計行は、集計された列が NULL になる。だからデータ本来の NULL と見分けがつかない
• 見分けるのが GROUPING(列)。小計・総計行なら 1、明細行なら 0 を返す。CASE WHEN GROUPING(部署)=1 THEN '総計' ELSE 部署 END でラベルを付ける
• 階層に沿わない特定の小計だけ欲しいなら GROUPING SETS で組み合わせを列挙する
• UNION ALL で総計を自前で足すより、ROLLUP のほうが短くて速い

コードは全部この記事の中にあります。まず「何を解決する道具なのか」から。

この構文が解決する課題 — 小計・総計を SQL 一発で

ROLLUP を知らない時代の書き方を、まず思い出しておきます。ここが出発点です。

部署ごとの合計と全体の総計を、1つの結果に出したい。ROLLUP を使わないと、こうなりがちです。

-- ROLLUP を知らない頃の書き方: 総計を UNION ALL で自前で足す
SELECT Department, SUM(Amount) AS Total
FROM Sales
GROUP BY Department
UNION ALL
SELECT N'総計', SUM(Amount)
FROM Sales;

動くには動く。でも同じテーブルを2回スキャンしてるし、部署と商品の2段階の小計まで欲しくなったら UNION ALL がどんどん増える。3段階になったら、もう目も当てられない。

ROLLUP は、この「小計・総計を足す」という作業を構文レベルで持っている道具です。GROUP BY の後ろに ROLLUP(...) と書くだけで、集計エンジンが小計行を勝手に組み立ててくれる。テーブルスキャンも1回で済む。

ここまでで分かったこと: 小計・総計を UNION ALL で自前で足すのは、スキャンも記述も増える。SQL Server にはそれ専用の ROLLUP / GROUPING SETS がある。

まず動かす土台 — サンプルテーブルを用意する

以降のコードは、この一時テーブルを前提に動かします。最初にこれだけ流しておいてください。

-- 部署 × 商品カテゴリ × 金額 の売上サンプル
DROP TABLE IF EXISTS #Sales;
CREATE TABLE #Sales (
    Department NVARCHAR(20),
    Category   NVARCHAR(20),
    Amount     INT
);
INSERT INTO #Sales (Department, Category, Amount) VALUES
(N'営業部', N'ハード', 120),
(N'営業部', N'ソフト',  80),
(N'開発部', N'ハード',  60),
(N'開発部', N'ソフト', 200);

営業部が合計 200、開発部が合計 260、全体で 460。この数字が小計・総計行として出てくるかを見ていきます。

ROLLUP の基本 — GROUP BY に小計と総計を足す

いちばんシンプルな形から。部署だけで ROLLUP してみます。

SELECT Department, SUM(Amount) AS Total
FROM #Sales
GROUP BY ROLLUP(Department);

これで営業部 200・開発部 260 に加えて、Department が NULL の行に 460（総計）が付いてきます。ROLLUP(Department) は「部署ごと」と「部署をまとめた総計」の2階層を出す、という意味です。

もう一段深くして、部署 × 商品でやってみます。ここが帳票っぽくなるところ。「あれ、この NULL の行なんや？？」ってなるのも、ちょうどここです。

SELECT Department, Category, SUM(Amount) AS Total
FROM #Sales
GROUP BY ROLLUP(Department, Category)
ORDER BY GROUPING(Department), Department, GROUPING(Category), Category;

実行結果:

明細（営業部/ハード=120 など）の下に、Category が NULL の行 = 部署ごとの小計（営業部の小計 200、開発部の小計 260）、いちばん下に Department も Category も NULL の行 = 総計 460 が出ています。

ROLLUP(Department, Category) は、右から順に列を畳んでいく。「部署×商品」→「部署ごと（商品をまとめた小計）」→「全部まとめた総計」という階層です。ORDER BY に GROUPING() を入れているのは、小計・総計を各グループの下に並べて帳票の見た目にするため。

ここまでで分かったこと: ROLLUP(A, B) は右から列を畳んで、Bの小計・Aの小計・総計を階層的に足す。小計・総計行では畳まれた列が NULL になる。

GROUPING() で「小計の NULL」と「本物の NULL」を見分ける

ここが業務SEが最初に引っかかりやすいところ。

小計・総計行では、集計された列が NULL で出てくる。でも、ちょっと考えてみてください。もし Department にデータ本来の NULL（部署未設定の売上とか）があったら？？ 小計行の NULL とデータの NULL が、見た目でまったく区別つかないんです。

WHERE Department IS NULL で総計行だけ取ろうとすると、部署未設定の明細まで混ざってくる。ここで詰まる。

これを解決するために用意されているのが GROUPING() です。

GROUPING(列) は、その行がその列で集計された小計・総計行なら 1、通常の明細行なら 0 を返します。NULL かどうかではなく、「ロールアップで畳まれた行かどうか」を直接教えてくれる。

これを使って、NULL のかわりにちゃんとしたラベルを付けます。

SELECT
    CASE WHEN GROUPING(Department) = 1 THEN N'★総計'
         ELSE Department END AS Department,
    CASE WHEN GROUPING(Category) = 1 AND GROUPING(Department) = 0 THEN N'（部署小計）'
         WHEN GROUPING(Category) = 1 THEN N''
         ELSE Category END AS Category,
    SUM(Amount) AS Total
FROM #Sales
GROUP BY ROLLUP(Department, Category)
ORDER BY GROUPING(Department), Department, GROUPING(Category), Category;

実行結果:

NULL が消えて、部署の小計行に「（部署小計）」、いちばん下に「★総計」が付きました。こんな感じで、これならもう帳票にそのまま流せます！！

肝は、GROUPING(Category)=1 AND GROUPING(Department)=0 で「商品は畳まれたが部署は畳まれていない行」＝部署小計を狙い撃ちしていること。両方 1 なら総計、という判定です。列が2つ以上ある時は GROUPING_ID() でまとめて判定する手もありますが、まずは GROUPING() の組み合わせで十分わかります。

ここまでで分かったこと: 小計・総計行の NULL は、データの NULL と見分けがつかない。GROUPING(列) が「畳まれた行か」を 1 / 0 で返すので、これでラベルを付けたり総計行だけ抽出したりする。

GROUPING SETS — 欲しい小計だけを狙って出す

ROLLUP は「階層的に全部の小計」を出します。便利なんだけど、階層に沿わない小計が欲しい時には向かない。

たとえば「部署ごとの小計」と「商品ごとの小計」は欲しいけど、「部署×商品の明細」は要らない、というケース。ROLLUP だと明細まで付いてくる。こういう時が GROUPING SETS の出番です。

GROUPING SETS は、欲しい集計の組み合わせを自分で列挙する書き方です。

SELECT
    CASE WHEN GROUPING(Department) = 1 THEN N'（全社）' ELSE Department END AS Department,
    CASE WHEN GROUPING(Category)   = 1 THEN N'（全商品）' ELSE Category   END AS Category,
    SUM(Amount) AS Total
FROM #Sales
GROUP BY GROUPING SETS ( (Department), (Category), () )
ORDER BY GROUPING(Department), GROUPING(Category);