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

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

社員マスタ
社員コード社員名部署コード年齢
1テスト社員1122
2テスト社員2230
3テスト社員3null20
売上明細
売上NO社員コード売上日売上金額会社コード
11201504012001
21201504023001
32201504081001
42201505011502
53201505055502
会社マスタ
会社コード会社名
1テスト会社1
2テスト会社2
3テスト会社3

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

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

社員マスタ
社員名会社名売上金額
テスト社員1会社名1500
テスト社員1小計500
テスト社員2会社名1100
テスト社員2会社名2250
テスト社員2小計350
テスト社員3会社名2550
テスト社員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会社名1500
テスト社員2会社名1100
テスト社員2会社名2250
テスト社員3会社名2550

次に、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会社名15001100
テスト社員1会社名15001null01
テスト社員2会社名11002100
テスト社員2会社名22502100
テスト社員2会社名23502null01
テスト社員3会社名25503100
テスト社員3会社名25503null01
テスト社員3会社名21400nullnull11

緑の行が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 WHERE句でデータを絞り込む

    WHERE句でデータを絞り込む方法について説明します。取得するデータを絞り込むには、WHERE句で条件を指定します。


  • SQL UPDATE文でデータを更新する

    UPDATE文でデータベースに新しいデータを更新する方法を紹介します。自身の値を参照する方法や、副問い合わせの結果をUPDATEする方法も合わせて紹介します。副問い合わせの結果を更新する場合、SETす...


  • SQL 副問い合わせの基本を理解する

    副問い合わせを説明します。副問い合わせとはSQL文の中に入れ子でSQL文を指定することをいいます。


  • SQL SELECT句の基本的な使い方

    SELECT句の基本的な使い方について説明します。


  • SQL SELECTした結果をINSERTで登録する

    SELECT-INSERT文なら、SELECTしたデータをそのまま一気にまとめて登録できるので、実装も簡単ですのでおすすめです。件数分ループでSQLを実行するよりも1回のSQLで一気にまとめて処理でき...


  • SQL ORACLEのROWNUMで行番号を取得する際の注意点

    oracleで行番号を取得する際の基本的な考え方と注意点を紹介します。行番号はROWNUMで取得することができます。注意しなければならないのは、ORDER BYでソートする場合です。ORDER BYは...


  • SQL チューニングでレスポンス改善に効果のあったポイント

    1回のSQLで一気にまとめて取得したほうがレスポンスは早いイメージがありますが、あまり大量のデータを扱うSQLを実行するとサーバーに負荷がかかってしまい、遅くなってしまうことがあります。


  • SQLとプログラミング言語の考え方の違い

    SQLは手続き型言語のように、上から考えるのではなく、集合を操作するという感覚で実装するとうまくいくように思います。また、上からではなく、逆に下から考えるという発送も必要だと思います。1ステップずつ実...


  • PL/SQL プロシージャでSQL文の中で条件分岐を行う

    PL/SQLプロシージャで、パラメータなどの値によってSQLの条件を変更するとき、IF文で条件分岐をすることができますが、IFとELSEで同じようなSQLの場合は、同じようなSQLを複数箇所に記述しな...