ITコーディネータのシュウです。

ショッピングセンターの外の広場が飾りつけられているのを、娘が以前夕方に撮った写真です。こういう飾りがあるだけで雰囲気は変わりますよね。これから、クリスマスに向けてもっといろいろな場所できれいな飾りが見れるようになるのではないかと楽しみです。

＜本日の題材＞
集計関数（小計、合計）

システムの帳票を出力する場合に、よく小計や合計を出力するケースがありますよね。これをSQL側で行いたいということも多々あるかと思います。今回は、GROUP BY句で集計を行う際に、小計や合計を出す方法について、取り上げてみたいと思います。

商品マスタのデータと、売上テーブルのデータが以下のような場合の、受注日ごと、及び商品の分類ごとの集計を出す場合を考えてみます。

SELECT * FROM 商品マスタ
ORDER BY 商品CD;
 

SELECT * FROM 売上テーブル
ORDER BY 受注番号;

受注日ごと、及び商品の分類ごとの集計を抽出します。（Oracleの場合）

SELECT A.受注日, B.分類
, SUM(A.商品数量) AS 数量, SUM(A.売上金額) 売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY A.受注日, B.分類
 ORDER BY A.受注日, B.分類;

次に、受注日ごと、及び商品の分類ごとの集計に、小計、合計を出すように、ROLLUP句を使用してみます。

SELECT A.受注日, B.分類
, SUM(A.商品数量) AS 数量, SUM(A.売上金額) 売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY ROLLUP(A.受注日, B.分類)
 ORDER BY A.受注日, B.分類;


ここで、小計、合計のときに NULL の表示になるのが嫌な場合には、以下のようなかたちで表現を変更することもできます。

SELECT
  DECODE(GROUPING(A.受注日), 1, '総合計', A.受注日) AS 受注日
, DECODE(GROUPING(A.受注日), 1, ' ', DECODE(GROUPING(B.分類), 1, '1日分小計', B.分類)) AS 分類
, SUM(A.商品数量) AS 数量
, SUM(A.売上金額) AS 売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY ROLLUP(A.受注日, B.分類)
 ORDER BY A.受注日, B.分類;

※GROUPING関数は、ROLLUPやCUBEの操作の場合に、小計や合計を表すNULLの場合に「1」を戻し、その他は「0」を戻すので、それを使って表示を変更しています。受注日のところの合計のところは、NULLではなく「総合計」、分類のところは、NULLではなく「1日分小計」としました。（総合計のところの分類は今回は空白にしました）

また、CUBE句を使用すると、指定された列のすべての組合せについての小計を作成します。

小計や合計のときの表現を考慮して
SELECT
  DECODE(GROUPING(A.受注日), 1, '総合計', A.受注日) AS 受注日
, CASE WHEN GROUPING(B.分類) = 0 THEN B.分類
       WHEN GROUPING(A.受注日) = 1 THEN ' '
       WHEN GROUPING(B.分類) = 1 THEN '1日分小計'
       END AS 分類
, SUM(A.商品数量) AS 数量
, SUM(A.売上金額) AS 売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY CUBE(A.受注日, B.分類)
 ORDER BY A.受注日, B.分類;

最後の総合計のところで、商品の分類ごとのデータが抽出されているのが ROLLUP句の場合と異なります。

●SQL Serverの場合
通常の集計について：

SELECT A.受注日, B.分類, SUM(A.商品数量)数量, SUM(A.売上金額)売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY A.受注日, B.分類
 ORDER BY A.受注日, B.分類;

ROLLUPを使った例は以下（こちらも総合計、小計の表現を設定したもの）：
SELECT
  CASE WHEN GROUPING(A.受注日) = 1 THEN '総合計'
       ELSE CAST(A.受注日 AS VARCHAR)
       END AS 受注日
, CASE WHEN GROUPING(B.分類) = 0 THEN B.分類
       WHEN GROUPING(A.受注日) = 1 THEN ' '
       WHEN GROUPING(B.分類) = 1 THEN '1日分小計'
       END AS 分類
, SUM(A.商品数量)数量
, SUM(A.売上金額)売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY A.受注日, B.分類 WITH ROLLUP
 ORDER BY 受注日, GROUPING(B.分類), B.分類;

※ROLLUP句は、GROP BY Col1, Col2,.. WITH ROLLUP となります。

CUBEを使った例は以下（こちらも総合計、小計の表現を設定したもの）：
SELECT
  CASE WHEN GROUPING(A.受注日) = 1 THEN '総合計'
       ELSE CAST(A.受注日 AS VARCHAR)
       END AS 受注日
, CASE WHEN GROUPING(B.分類) = 0 THEN B.分類
      WHEN GROUPING(A.受注日) = 1 THEN ' '
       WHEN GROUPING(B.分類) = 1 THEN '1日分小計'
       END AS 分類
, SUM(A.商品数量)数量
, SUM(A.売上金額)売上金額
  FROM 売上テーブル A
  JOIN 商品マスタ B ON A.商品CD = B.商品CD
 GROUP BY A.受注日, B.分類 WITH CUBE
 ORDER BY 受注日, GROUPING(B.分類), B.分類;

※CUBE句は、GROP BY Col1, Col2,.. WITH CUBE となります。
Oracleのときと同様な結果が出ました。

ちなみに、MySQLの場合も ROLLUP・CUBE句は、SQL Serverと同じく
  GROP BY Col1, Col2,.. WITH ROLLUP
　GROP BY Col1, Col2,.. WITH CUBE
となります。Oracleが独自のようですね。

今日は以上まで