zukucode
主にWEB関連の情報を技術メモとして発信しています。

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

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

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

取得したい結果
region_nameprefecture_namescore
東北地方青森県9
東北地方岩手県6
東北地方宮城県6
東北地方計null21
関東地方東京都7
関東地方神奈川県0
関東地方計null7
中部地方愛知県6
中部地方静岡県13
中部地方岐阜県0
中部地方計null19
総合計null47

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

sales(売上)
sales_idprefecture_idscore
113
216
322
424
536
646
741
666
771
878
974
prefecture(都道府県)
prefecture_idregion_idprefecture_name
11青森県
21岩手県
31宮城県
42東京都
52神奈川県
63愛知県
73静岡県
83岐阜県
region(地方)
region_idregion_name
1東北地方
2関東地方
3中部地方

都道府県ごとに集計

上記の集計を行う前に、まずは都道府県ごとに集計する処理として、SQL GROUP BYで複数の行を集計して1行で取得するで紹介したように、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_nameprefecture_namescore
東北地方青森県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_namescore
東北地方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_idprefecture_idregion_nameprefecture_namescore
11東北地方青森県9
12東北地方岩手県6
13東北地方宮城県6
1null東北地方青森県21
24関東地方東京都7
25関東地方神奈川県0
2null関東地方東京都7
36中部地方愛知県6
37中部地方静岡県13
38中部地方岐阜県0
3null中部地方静岡県19
nullnull東北地方東京都47

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

prefecture_idnullの行は都道府県(prefecture_id)の合計行、すなわち地方計となります。

また、region_idnullの行は地方(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_sumprefecture_sumregion_nameprefecture_namescore
00東北地方青森県9
00東北地方岩手県6
00東北地方宮城県6
01東北地方計null21
00関東地方東京都7
00関東地方神奈川県0
01関東地方計null7
00中部地方愛知県6
00中部地方静岡県13
00中部地方岐阜県0
01中部地方計null19
11総合計null47

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

region_sumprefecture_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_idnullとなるので、GROUPING関数を指定しなくても意図したとおりにソートされます。


関連記事

  • Oracle 文字列の中で指定した文字を削除する

    Oracleで文字列の中で指定した文字を削除する方法を紹介します。REPLACEを使用して削除したい文字をNULL(空文字)に置き換えることで削除します。Oracleは空文字をNULLとして扱うので、...


  • SQL GROUP BYで複数の行を集計して1行で取得する

    GROUP BY 列名で集約したい列を指定します。上記例の場合は部署ごとに集約したいのでdepartment_idを指定しています。GROUP BYで指定した列以外の列をSELECT句で取得したい場合...


  • SQL FROM句で複数のテーブルを結合するポイント

    FROM句では取得したいテーブルのテーブル名を指定します。複数のテーブルを結合する場合はまず結合したいテーブルをJOINのあとに指定します。そしてONのあとに結合条件を指定します。結合条件を指定せずに...


  • SQL DISTINCTで重複行を1行のみ表示する方法

    SELECTのあとにDISTINCTをつけると、同一のレコードを1行のみ取得するようになります。上記例ではid=1と4のレコードの取得結果は同じになるので、本来は2行出力されるはずのレコードが、DIS...


  • PostgreSQL 現在のシステム日付・時刻を取得する

    OracleのSYSDATEのように、PostgreSQLで現在のシステム日付や時刻を取得する方法を紹介します。YYYY/MM/DDやYYYY-MM-DDのように、date型を取得するにはCURREN...


  • SQL FROM句での副問合わせの基本的な考え方と注意点

    FROM句で副問合わせを行うと、SELECTした結果を1つのテーブルとして、他のテーブルと結合できます。SELECTした結果をVIEWとして予め定義しておくことができますが、FROM句で副問合わせでは...


  • PostgreSQL 日付の加算・減算を行う

    PostgreSQLで日付の加算・減算を行う方法を紹介します。日付型のカラムに対しては、数字の計算と同じ要領で行うことができます。YYYYMMDD形式などの文字列型のカラムの場合は、日付型に変換してか...


  • PostgreSQL ISNULLやNVLのようにNULLを判定する方法

    PostgreSQLでSQL ServerのISNULLやOracleのNVLのように、NULLの場合に別の値を取得するにはCOALESCEを使用します。使い方はISNULLやNVLと同じように、第一...


  • PL/SQL 変数を宣言して値を設定する

    PL/SQLで変数を宣言して値を設定する方法を紹介します。ASとBEGINの間で変数を宣言します。宣言と同時に値をセット(初期化)も可能です。変数への代入は=ではなく:=で行います。


  • PL/SQL TRUNCATE TABLEをストアドプロシージャに定義する

    PL/SQLでTRUNCATE処理をストアドプロシージャに定義する方法を紹介します。そのまま定義すると「記号TABLEが見つかりました。」のコンパイルエラーになってしまうので、動的SQLで定義します。