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.会社コード)