pontz_rwのブログ

プログラミング等の備忘録

小計、合計行を出力する

sql

小計行や合計行を出力したい場合に ROOLUP を使用できます。

例

次のようなデータを使って ROLLUP の動作を見ていきます。

SELECT sale_id,
       area_id,
       amount
  FROM (VALUES ('S001', 'A001', 100),
               ('S001', 'A002', 150),
               ('S001', 'A003', 100),
               ('S002', 'A001', 100),
               ('S002', 'A002', 150),
               ('S003', 'A001', 200)) AS Sales(sale_id, area_id, amount)
sale_id area_id amount
S001 A001 100
S001 A002 150
S001 A003 100
S002 A001 100
S002 A002 150
S003 A001 200

売上一覧と sale_id 別の売上

まずは売上一覧と sale_id 別の売上を同時に取得してみましょう。次のような取得結果を目指します。

sale_id area_id amount
S001 A001 100
S001 A002 150
S001 A003 100
S001 350
S002 A001 100
S002 A002 150
S002 250
S003 A001 200
S003 200

一般的には売上一覧と sale_id 別の売上を別々に取得し、UNION で結合して取得します。

WITH Sales AS
(SELECT sale_id,
        area_id,
        amount
   FROM (VALUES ('S001', 'A001', 100),
                ('S001', 'A002', 150),
                ('S001', 'A003', 100),
                ('S002', 'A001', 100),
                ('S002', 'A002', 150),
                ('S003', 'A001', 200)) AS Sales(sale_id, area_id, amount))
-- 売上一覧
SELECT sale_id,
       area_id,
       amount
  FROM Sales
 UNION ALL
-- sale_id 別売上
SELECT sale_id,
       NULL AS area_id,
       SUM (amount) AS amount
  FROM Sales
 GROUP BY sale_id
 ORDER BY sale_id,
          area_id NULLS LAST

ORDER BY 句で sale_id 別の売上が、対応する sale_id の末尾にくるように調整しています。

では、ROLLUP を使って上記のクエリと同じ結果を取得してみましょう。

WITH Sales AS
(SELECT sale_id,
        area_id,
        amount
   FROM (VALUES ('S001', 'A001', 100),
                ('S001', 'A002', 150),
                ('S001', 'A003', 100),
                ('S002', 'A001', 100),
                ('S002', 'A002', 150),
                ('S003', 'A001', 200)) AS Sales(sale_id, area_id, amount))
SELECT sale_id,
       area_id,
       SUM (amount) AS amount
  FROM Sales
 GROUP BY sale_id,
          ROLLUP (area_id)
 ORDER BY sale_id,
          area_id NULLS LAST

UNION を使ったクエリとの違いは、次の通りです。

  WITH Sales AS
  (SELECT sale_id,
          area_id,
          amount
     FROM (VALUES ('S001', 'A001', 100),
                  ('S001', 'A002', 150),
                  ('S001', 'A003', 100),
                  ('S002', 'A001', 100),
                  ('S002', 'A002', 150),
                  ('S003', 'A001', 200)) AS Sales(sale_id, area_id, amount))
- SELECT sale_id,
-        area_id,
-        amount
-   FROM Sales
-  UNION ALL
  SELECT sale_id,
-        NULL AS area_id,
+        area_id,
         SUM (amount) AS amount
    FROM Sales
-  GROUP BY sale_id
+  GROUP BY sale_id,
+           ROLLUP (area_id)
   ORDER BY sale_id,
            area_id NULLS LAST

sale_id ごとの小計を取得する場合は、ROLLUP (sale_id) ではなく ROLLUP (area_id) を使うので、注意しましょう。

sale_id ごとの売上と総売上

続いて sale_id ごとの売上と総売上を取得してみましょう。

WITH Sales AS
(SELECT sale_id,
        area_id,
        amount
   FROM (VALUES ('S001', 'A001', 100),
                ('S001', 'A002', 150),
                ('S001', 'A003', 100),
                ('S002', 'A001', 100),
                ('S002', 'A002', 150),
                ('S003', 'A001', 200)) AS Sales(sale_id, area_id, amount))
-- sale_id 別売上
SELECT sale_id,
       SUM (amount) AS amount
  FROM Sales
 GROUP BY sale_id
 UNION ALL
-- 総売上
SELECT NULL AS sale_id,
       SUM (amount) AS amount
  FROM Sales
 ORDER BY sale_id NULLS LAST
sale_id amount
S001 350
S002 250
S003 200
800

ROLLUP を使って同じ結果を取得してみましょう。

WITH Sales AS
(SELECT sale_id,
        area_id,
        amount
   FROM (VALUES ('S001', 'A001', 100),
                ('S001', 'A002', 150),
                ('S001', 'A003', 100),
                ('S002', 'A001', 100),
                ('S002', 'A002', 150),
                ('S003', 'A001', 200)) AS Sales(sale_id, area_id, amount))
SELECT sale_id,
       SUM (amount) AS amount
  FROM Sales
 GROUP BY ROLLUP (sale_id)
 ORDER BY sale_id NULLS LAST

UNION を使ったクエリとの違いは、次の通りです。

  WITH Sales AS
  (SELECT sale_id,
          area_id,
          amount
     FROM (VALUES ('S001', 'A001', 100),
                  ('S001', 'A002', 150),
                  ('S001', 'A003', 100),
                  ('S002', 'A001', 100),
                  ('S002', 'A002', 150),
                  ('S003', 'A001', 200)) AS Sales(sale_id, area_id, amount))
  SELECT sale_id,
         SUM (amount) AS amount
    FROM Sales
-  GROUP BY sale_id
+  GROUP BY ROLLUP (sale_id)
-  UNION ALL
- SELECT NULL AS sale_id,
-        SUM (amount) AS amount
-   FROM Sales
   ORDER BY sale_id NULLS LAST

GROUP BY ROLLUP (sale_id) が sale_id 別の売上と、それらをまとめた総売上を求めるのに必要であることが分かります。

これまでの結果をまとめる

では、最後にこれまでの結果をまとめたものを取得するクエリを、ROLLUP を使って求めてみましょう。次のような取得結果を目指します。

sale_id area_id amount
S001 A001 100
S001 A002 150
S001 A003 100
S001 350
S002 A001 100
S002 A002 150
S002 250
S003 A001 200
S003 200
800

sale_id と area_id の2つを ROLLUP に指定します。

WITH Sales AS
(SELECT sale_id,
        area_id,
        amount
   FROM (VALUES ('S001', 'A001', 100),
                ('S001', 'A002', 150),
                ('S001', 'A003', 100),
                ('S002', 'A001', 100),
                ('S002', 'A002', 150),
                ('S003', 'A001', 200)) AS Sales(sale_id, area_id, amount))
SELECT sale_id,
       area_id,
       SUM (amount) AS amount
  FROM Sales
 GROUP BY ROLLUP (sale_id,
                  area_id)
 ORDER BY sale_id,
          area_id NULLS LAST

以上より sale_id 別の小計と合計を同時に取得することができます。

見た目を整える

集計行の集計キーは NULL となるので、COALESCE() を使うなどして見た目を整えることもできます。

SELECT COALESCE(sale_id, '') AS sale_id,
       COALESCE(area_id, CASE
                         WHEN sale_id IS NULL THEN '合計'
                         ELSE '小計'
                         END) AS area_id,
       SUM(amount) AS amount
  FROM (VALUES ('S001', 'A001', 100),
               ('S001', 'A002', 150),
               ('S001', 'A003', 100),
               ('S002', 'A001', 100),
               ('S002', 'A002', 150),
               ('S003', 'A001', 200)) AS Sales(sale_id, area_id, amount)
 GROUP BY ROLLUP (sale_id, area_id)
sale_id area_id amount
S001 A001 100
S001 A002 150
S001 A003 100
S001 小計 350
S002 A001 100
S002 A002 150
S002 小計 250
S003 A001 200
S003 小計 200
合計 800

また GROUPING() を使うことで、どの行が集計行であるのかが分かります。

SELECT GROUPING(sale_id) AS total_sum,
       GROUPING(area_id) AS sale_id_sum,
       CASE WHEN GROUPING(sale_id) = 1 THEN ''
            ELSE sale_id
       END AS sale_id,
       CASE WHEN GROUPING(sale_id) = 1 THEN '合計'
            WHEN GROUPING(area_id) = 1 THEN '小計'
            ELSE area_id
       END AS area_id,
       SUM(amount) AS amount
  FROM (VALUES ('S001', 'A001', 100),
               ('S001', 'A002', 150),
               ('S001', 'A003', 100),
               ('S002', 'A001', 100),
               ('S002', 'A002', 150),
               ('S003', 'A001', 200)) AS Sales(sale_id, area_id, amount)
 GROUP BY ROLLUP (sale_id, area_id)
total_sum sale_id_sum sale_id area_id amount
0 0 S001 A001 100
0 0 S001 A002 150
0 0 S001 A003 100
0 1 S001 小計 350
0 0 S002 A001 100
0 0 S002 A002 150
0 1 S002 小計 250
0 0 S003 A001 200
0 1 S003 小計 200
1 1 合計 800