SQL ROLLUPで集計して小計行と合計行を取得する

SQLで小計行や合計行を出力して集計するにはGROUP BYの処理でROLLUPを使用します。

合計行や小計行を含めて以下のような取得をしたい場合があります。

取得したい結果
region_name	prefecture_name	score
東北地方	青森県	9
東北地方	岩手県	6
東北地方	宮城県	6
東北地方計	null	21
関東地方	東京都	7
関東地方	神奈川県	0
関東地方計	null	7
中部地方	愛知県	6
中部地方	静岡県	13
中部地方	岐阜県	0
中部地方計	null	19
総合計	null	47

上記出力結果の集計元として、都道府県（prefecture）ごとの売上のテーブル（sales）があるとします。また、都道府県は地方（region）に属するとします。

sales（売上）
sales_id	prefecture_id	score
1	1	3
2	1	6
3	2	2
4	2	4
5	3	6
6	4	6
7	4	1
6	6	6
7	7	1
8	7	8
9	7	4

prefecture（都道府県）
prefecture_id	region_id	prefecture_name
1	1	青森県
2	1	岩手県
3	1	宮城県
4	2	東京都
5	2	神奈川県
6	3	愛知県
7	3	静岡県
8	3	岐阜県

region（地方）
region_id	region_name
1	東北地方
2	関東地方
3	中部地方

都道府県ごとに集計

上記の集計を行う前に、まずは都道府県ごとに集計する処理として、SQL GROUP BYで複数の行を集計して１行で取得するで紹介したように、GROUP BYで集計します。

SELECT
  MAX(T1.region_name) region_name,
  MAX(T2.prefecture_name) prefecture_name,
  SUM(NVL(T3.score, 0)) score
FROM
  region T1
  JOIN prefecture T2
    ON T1.region_id = T2.region_id
  LEFT JOIN sales T3
    ON T2.prefecture_id = T3.prefecture_id
GROUP BY
  T1.region_id,
  T2.prefecture_id
ORDER BY
  T1.region_id,
  T2.prefecture_id

取得結果
region_name	prefecture_name	score
東北地方	青森県	9
東北地方	岩手県	6
東北地方	宮城県	6
関東地方	東京都	7
関東地方	神奈川県	0
中部地方	愛知県	6
中部地方	静岡県	13
中部地方	岐阜県	0

地方ごとに集計

合計行の値が正しいか確認するために、地方ごとの集計結果も一応確認します。

地方ごとの集計も同様にGROUP BYを使用します。

SELECT
  MAX(T1.region_name) region_name,
  SUM(NVL(T3.score, 0)) score
FROM
  region T1
  JOIN prefecture T2
    ON T1.region_id = T2.region_id
  LEFT JOIN sales T3
    ON T2.prefecture_id = T3.prefecture_id
GROUP BY
  T1.region_id
ORDER BY
  T1.region_id

取得結果
region_name	score
東北地方	21
関東地方	7
中部地方	19

ROLLUPで合計行を取得

小計行と合計行を出力するために、GROUP BY句の中でROLLUPを指定します。

SELECT
  T1.region_id,
  T2.prefecture_id,
  MAX(T1.region_name) region_name,
  MAX(T2.prefecture_name) prefecture_name,
  SUM(NVL(T3.score, 0)) score
FROM
  region T1
  JOIN prefecture T2
    ON T1.region_id = T2.region_id
  LEFT JOIN sales T3
    ON T2.prefecture_id = T3.prefecture_id
GROUP BY
  T1.region_id,
  T2.prefecture_id
  ROLLUP (
    T1.region_id,
    T2.prefecture_id
  )
ORDER BY
  T1.region_id,
  T2.prefecture_id

取得結果（背景赤の部分は修正が必要です。修正方法は後述します。）
region_id	prefecture_id	region_name	prefecture_name	score
1	1	東北地方	青森県	9
1	2	東北地方	岩手県	6
1	3	東北地方	宮城県	6
1	null	東北地方	青森県	21
2	4	関東地方	東京都	7
2	5	関東地方	神奈川県	0
2	null	関東地方	東京都	7
3	6	中部地方	愛知県	6
3	7	中部地方	静岡県	13
3	8	中部地方	岐阜県	0
3	null	中部地方	静岡県	19
null	null	東北地方	東京都	47

ROLLUPで囲った地方（region_id）ごとの合計行と、都道府県（prefecture_id）ごとの合計行が出力されます。

prefecture_idがnullの行は都道府県（prefecture_id）の合計行、すなわち地方計となります。

また、region_idがnullの行は地方（region_id）の合計行、すなわち総合計となります。

上記赤背景の部分のように、合計行に東北地方と出力されてしまうのは集計関数が適用されているからです。

例えばregion_name列はMAX(T1.region_name)ですが、合計行にもこれが適用されます。具体的には集計対象の行となる青森県、岩手県、宮城県の最大値（MAX）が出力されます。

合計行で集計するべきscoreに関しては、SUMで集計しているため、集計対象の合計（SUM）が出力されます。

ROLLUPの考え方

ROLLUPの考え方として、都道府県（prefecture_id）の集計行は地方計になり、地方（region_id）の集計行は総合計になります。

地方計を表示したい場合は都道府県（prefecture_id）をROLLUPに指定して、総合計を表示したい場合は、地方（prefecture_id）をROLLUPに指定します。

地方計を出力したいからといって、地方（prefecture_id）をROLLUPに指定しないように注意が必要です。

地方（prefecture_id）ではなく地方の集計もとである都道府県（prefecture_id）にROLLUPを指定するようにします。

これがROLLUPのハマりポイントですので注意する必要があります。

GROUPING関数で合計行かどうか判定する

上記の背景赤の部分は、小計行または合計行なので〜計として出力する必要があります。

「集計行かどうか」という判定はGROUPING(集計列)で判定できます。

以下のようにGROUPING(集計列)で集計行かどうか判定してCASE文で出力内容を分岐します。

わかりやすくするために、GROUPING(集計列)の結果もそのまま出力しています。

SELECT
  GROUPING(T1.region_id) region_sum,
  GROUPING(T2.prefecture_id) prefecture_sum,
  MAX(T1.region_name) region_name,
  CASE
    WHEN GROUPING(T1.region_id) = 1 THEN
      -- 合計行の場合
      '総合計'
    WHEN GROUPING(T2.prefecture_id) = 1 THEN
      -- 小計行の場合
      MAX(T1.region_name) || '計'
    ELSE
      -- 通常行の場合
      MAX(T1.region_name)
    END region_name,
  MAX(T2.prefecture_name) prefecture_name,
  CASE
    WHEN GROUPING(T1.region_id) = 1 THEN
      -- 合計行の場合
      null
    WHEN GROUPING(T2.prefecture_id) = 1 THEN
      -- 小計行の場合
      null
    ELSE
      -- 通常行の場合
      MAX(T2.prefecture_name)
    END prefecture_name,
  SUM(NVL(T3.score, 0)) score
FROM
  region T1
  JOIN prefecture T2
    ON T1.region_id = T2.region_id
  LEFT JOIN sales T3
    ON T2.prefecture_id = T3.prefecture_id
GROUP BY
  ROLLUP(
    T1.region_id,
    T2.prefecture_id
  )
ORDER BY
  GROUPING(T1.region_id),
  T1.region_id,
  GROUPING(T2.prefecture_id),
  T2.prefecture_id

取得結果
region_sum	prefecture_sum	region_name	prefecture_name	score
0	0	東北地方	青森県	9
0	0	東北地方	岩手県	6
0	0	東北地方	宮城県	6
0	1	東北地方計	null	21
0	0	関東地方	東京都	7
0	0	関東地方	神奈川県	0
0	1	関東地方計	null	7
0	0	中部地方	愛知県	6
0	0	中部地方	静岡県	13
0	0	中部地方	岐阜県	0
0	1	中部地方計	null	19
1	1	総合計	null	47

これで合計行や小計行を出力できました。

region_sumやprefecture_sumといった小計行や合計行の情報は、画面表示時に入力制御などに必要となることが多いので、上記のように一緒に取得することが多いです。

表示順を指定する

合計行を含めて表示順を正しく行うには、ORDEY BYでもGROUPING関数を使用します。

以下のように、region_idなどの通常行のソート順を指定する前に、GROUPING(T1.region_id)を指定します。

GROUPING(T1.region_id)は通常行が0、集計行が1となるので、通常行→集計行の順番になります。

ORDER BY
  GROUPING(T1.region_id),
  T1.region_id,
  GROUPING(T2.prefecture_id),
  T2.prefecture_id

今回のような場合は、合計行のregion_idがnullとなるので、GROUPING関数を指定しなくても意図したとおりにソートされます。