SQL ROLLUP文で小計行・合計行を出力する (oracle, sql server)

「社員マスタ」と「売上明細」というテーブルがあります。売上明細テーブルには、社員の売上情報が格納されています。

社員マスタ
社員コード 社員名 部署コード 年齢
1 テスト社員1 1 22
2 テスト社員2 2 30
3 テスト社員3 null 20
売上明細
売上NO 社員コード 売上日 売上金額 会社コード
1 1 20150401 200 1
2 1 20150402 300 1
3 2 20150408 100 1
4 2 20150501 150 2
5 3 20150505 550 2
会社マスタ
会社コード 会社名
1 テスト会社1
2 テスト会社2
3 テスト会社3

社員ごとに、会社ごとの売上合計を表示し、社員ごとにすべての会社の小計行も出力したい、また最終行に、全社員の全会社の合計行も表示したい。

課題を読んだだけではイメージがわかないと思うので、今回は出力したい結果から示します。課題のSQL文は本ページの最後に記載してあります。

:社員マスタ |社員名|会社名|売上金額| |-|-|-|-| |テスト社員1|会社名1|500| |テスト社員1|小計|500| |テスト社員2|会社名1|100| |テスト社員2|会社名2|250| |テスト社員2|小計|350| |テスト社員3|会社名2|550| |テスト社員3|小計|550| |全社員|合計|1400|

ROLLUPという文をGROUP BY句で指定すると、会社計と社員計の合計行を出力することができます。

言葉で説明するのは難しいので、1ステップずつSQLを示しながら、上記結果を出力するSQLを作っていきます。

まずは小計行と合計行の出力は考えず、社員ごとかつ会社ごとに売上明細のデータを集計してみます。

SQLは下記のようになります。

SELECT MAX(T1.社員名) AS 社員名
    ,MAX(T3.会社名) AS 会社名
    ,SUM(T2.売上金額) AS 売上金額
FROM 社員マスタ AS T1
    LEFT JOIN 売上明細 AS T2
    ON T1.社員コード = T2.社員コード
    LEFT JOIN 会社マスタ AS T3
    ON T2.会社コード = T3.会社コード
GROUP BY T1.社員コード, T3.会社コード
社員マスタ
社員名 会社名 売上金額
テスト社員1 会社名1 500
テスト社員2 会社名1 100
テスト社員2 会社名2 250
テスト社員3 会社名2 550

次に、GROUP BY句の最後に、ROLLUP文を指定します。 わかりやすくするために、社員コードや会社コードなども出力します。 SQLは下記のようになります。

SELECT MAX(T1.社員名) AS 社員名
    ,MAX(T3.会社名) AS 会社名
    ,SUM(T2.売上金額) AS 売上金額
    ,T1.社員コード AS 社員コード
    ,T3.会社コード AS 会社コード
    ,GROUPING(T1.社員コード) AS 社員計
    ,GROUPING(T3.会社コード) AS 会社計
FROM 社員マスタ AS T1
    LEFT JOIN 売上明細 AS T2
    ON T1.社員コード = T2.社員コード
    LEFT JOIN 会社マスタ AS T3
    ON T2.会社コード = T3.会社コード
GROUP BY ROLLUP(T1.社員コード, T3.会社コード)

:社員マスタ |社員名|会社名|売上金額|社員コード|会社コード|社員計|会社計| |-|-|-|-|-|-|-|-| |テスト社員1|会社名1|500|1|1|0|0| |+テスト社員1|+会社名1|+500|+1|+null|+0|+1| |テスト社員2|会社名1|100|2|1|0|0| |テスト社員2|会社名2|250|2|1|0|0| |+テスト社員2|+会社名2|+350|+2|+null|+0|+1| |テスト社員3|会社名2|550|3|1|0|0| |+テスト社員3|+会社名2|+550|+3|+null|+0|+1| |+テスト社員3|+会社名2|+1400|+null|+null|+1|+1|

緑の行がROLLUP文によって出力された集計行です。

ROLLUP文で指定した集計単位の上位の集計単位で集計行が出力されます。

今回の例だと、ROLLUP(T1.社員コード, T3.会社コード)なので、会社コードの上位の集計単位は社員コードで、社員コードの上位の集計単位はSQL全体になります。

よって、社員コードが変わるごとに社員コードごとの集計行が出力され、SQLの最終行に表全体の集計行が出力されます。

集計対象が変わるだけで、集計方法は変わりません。集計行の社員名や会社名が上記のように表示されるのは、集計対象の中のMAX値を取得しているためです。売上金額は集計対象の値をSUMしているため、合計値として表示されています。

SELECT句でGROUPINGという関数を使用すれば、どの行が集計行かどうかがわかります。集計対象の列の場合は1が出力され、それ以外は0が出力されます。

CASE文でGROUPINGの値によって場合分けをすれば、課題の実行結果を得ることができます。

SQLは下記のようになります。

SELECT CASE WHEN GROUPING(T1.社員コード) = 1 THEN '全社員'
        ELSE MAX(T1.社員名) END AS 社員名
    ,CASE WHEN GROUPING(T1.社員コード) = 1 THEN '合計'
        GROUPING(T1.会社コード) = 1 THEN '小計'
        ELSE MAX(T3.会社名) END AS 会社名
    ,SUM(T2.売上金額) AS 売上金額
FROM 社員マスタ AS T1
    LEFT JOIN 売上明細 AS T2
    ON T1.社員コード = T2.社員コード
    LEFT JOIN 会社マスタ AS T3
    ON T2.会社コード = T3.会社コード
GROUP BY ROLLUP(T1.社員コード, T3.会社コード)

また、今回はたまたま綺麗な並び順で表示されましたがORDER BYを指定して、明示的に並び順を指定する場合は、下記のようになります。

SELECT CASE WHEN GROUPING(T1.社員コード) = 1 THEN '全社員'
        ELSE MAX(T1.社員名) END AS 社員名
    ,CASE WHEN GROUPING(T1.社員コード) = 1 THEN '合計'
        GROUPING(T1.会社コード) = 1 THEN '小計'
        ELSE MAX(T3.会社名) END AS 会社名
    ,SUM(T2.売上金額) AS 売上金額
FROM 社員マスタ AS T1
    LEFT JOIN 売上明細 AS T2
    ON T1.社員コード = T2.社員コード
    LEFT JOIN 会社マスタ AS T3
    ON T2.会社コード = T3.会社コード
GROUP BY ROLLUP(T1.社員コード, T3.会社コード)
ORDER BY T1.社員コード
    ,GROUPING(T1.社員コード) --同一の社員コードで通常行は0、合計行は1なので、合計行が同一社員の一番最後の行に出力される
    ,T1.会社コード
    ,GROUPING(T3.会社コード)

関連記事

  • SQL WITH句でVIEWを作成し重複するSQL文をまとめる

    WITH句でVIEWを作成し重複するSQL文をまとめる方法を説明します。WITH句を使えば1つの副問い合わせ(SQL)を複数の箇所で使いまわすことができます。


  • SQL OVER句の分析関数で効率よくデータを集計する

    分析関数を使用すれば、効率よく簡単にデータを集計することができます。集計関数COUNTの後にOVERを指定して、分析関数として集計します。OVERの中のPARTITION BYとORDER BYで分析...


  • SQL ORDER BY句で取得したデータを並び替える

    ORDER BY句で取得したデータを並び替える(ソート)方法について説明します。取得するデータを並び替えるには、ORDER BY句でソートしたいカラムを指定します。


  • SQL oracleでnullを比較する際の注意点

    oracleでnullを扱うときに注意する点をまとめました。値がnullのデータを取得したいときは、イコール(=)ではなくIS NULLで取得する必要があります。nullも含めて取得する場合は、NVL...


  • SQL MERGE文でINSERTとUPDATEを一回で行う

    テーブルにデータがすでに存在している場合は更新(UPDATE)、存在していない場合は登録(INSERT)をしたい場合があると思います。MERGE文を使えば一回のSQLで、INSERTとUPDATEの処...


  • SQL PARTITION BYで効率よくデータを取得する

    PARTITION BYをうまく使用すれば、効率よく簡単にデータを集計だけでなく、取得することができます。PATITION BYで部署コード単位で集計することができるようになります。売上累計の降順で並...


  • SQL INSERT文でデータを登録する

    INSERT文でデータベースに新しいデータを登録する方法を紹介します。テーブルに新しいデータを登録したい場合、以下のようなSQLを実行します。


  • SQL インデックスが効かない場合の原因と対処法

    テーブルにインデックスを張っていても実行計画を見るとなぜかテーブルがフルスキャンされていて「なんで?」となった経験があるかと思います。せっかく張ったインデックスが効かないSQLの実装例と対処法を紹介し...


  • SQL IN句をEXISTS句に変換する方法

    IN句をEXISTS句に変換する方法を紹介します。IN句よりもEXISTS句のほうがパフォーマンスが良くなる場合が多いので、主にレスポンス対策としてこの書き換えを行うことが多いと思います。


  • SQL IN句に1000件以上要素を指定したときのエラーの対処法

    SQLのIN句に1000件以上指定するとORA-01795: リストに指定できる式の最大数は1000です。のエラーが発生してしまいます。EXISTS句に書き換えられる場合は書き換えてしまうのが一番簡単...