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句を使えば1つの副問い合わせ(SQL)を複数の箇所で使いまわすことができます。例としてSQL 副問い合わせの基本を理解するで扱ったFROM句の副問い合わせのSQLをおさらいします。※今回は学習...


  • SQL WHERE句でデータを絞り込む

    「社員マスタ」と「部署マスタ」というテーブルがあります。今回は、社員マスタに「年齢」カラムを追加してあります。年齢が25歳以上で営業部に所属する社員を表示したい。SQLは下記のようになります。取得する...


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

    UPDATE文でデータベースに新しいデータを更新する方法を紹介します。「社員マスタ」というテーブルがあります。カラムは下記のとおりです。テスト社員2の年齢を31歳に更新したい場合、以下のようなSQLを...


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

    副問い合わせとはSQL文の中に入れ子でSQL文を指定することをいいます。例えば下記のSQL文は副問い合わせを使用しています。副問い合わせはWHERE句だけでなく、SELECT句やFROM句でも使用する...


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

    SELECT句ではデータベースの指定したテーブルからデータを取得する際に、どの項目を取得するか指定します。下記に具体的な使い方を紹介します。「商品マスタ」というテーブルがあります。カラムは下記のとおり...


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

    SELECTとINSERTを組み合わせて効率よくデータを登録することができます。SELECT文で取得したデータをINSERT文でデータベースに新しいデータを登録する方法を紹介します。「社員マスタ」とい...


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

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


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

    SQLのレスポンス改善に効果のあった対応内容をまとめます。環境はOracleですが、他のデータベースでも参考になると思います。パフォーマンスは同じようなSQLでもテーブルのインデックスなどの構成やデー...


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

    SQLは他のJAVAやC言語などのプログラミング言語とは異なる考え方で、実装を行う必要があります。プログラミング言語は基本的には上から順番に実行されていきます。これを手続き型言語と呼ぶことが多いのです...


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

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