WITH句を使った最終行に合計行を追加する方法

postgreSQLで集計した後で最終行に各項目の合計値を出したい場合があると思います。
よくある方法としてUNION ALLを使って合計行を計算するというのがありますが、
同じようなSQLを複数回書く必要があり、複雑なSQLの場合手間だし、
メンテナンス性が悪いというのがあります。
そこで今回はWITHを使った合計行の出し方を紹介します。

※この記事はPostgreSQL 13.5 で動作確認してます。

WITH句とは

サブクエリを一時テーブルのように扱えるようになる機能です。

テーブル

WITHを使わないソース

WITH句を使わないで日別集計を行う場合は下記のようになります。

SELECT 
   売上日::varchar
  ,SUM(金額 * 個数) AS 合計金額 
  ,SUM(個数) AS 合計個数
FROM テーブル
GROUP BY 売上日
UNION ALL 
SELECT 
   '合計'
  ,SUM(金額 * 個数) AS 合計金額 
  ,SUM(個数) AS 合計個数
FROM テーブル
ORDER BY 売上日
--※合計行で文字列を入れるため売上日をvarcharに変換しています

WITH句を使ったソース

WITH tmp AS (
  SELECT 
     売上日::varchar
    ,SUM(金額 * 個数) AS 合計金額 
    ,SUM(個数) AS 合計個数
  FROM テーブル
  GROUP BY 売上日
  )
SELECT * FROM tmp
UNION ALL
SELECT
   '合計'
  ,SUM(合計金額)
  ,SUM(合計個数)
FROM tmp
ORDER BY 売上日

結果

結果は同じですが、WITH句を使う場合は短く、サブクエリを2回書く必要がなくなってます。

まとめ

WITH句で一時テーブルを作ることによって複雑な検索結果に対して簡単に合計行を追加することができます。
また、WITH句を使うことによって修正したい時に少ない修正で済むようになるので間違った出力結果を出す可能性を少なくできます。
複雑なSQLをゴリゴリ書く場合はWITH句は何かと便利に使えるかと思います！

補足

他にも合計や小計が必要な場合はGROUPING SETS関数、CUBE関数、ROLLUP関数
辺りも便利に使うことができます。