PostgreSQL/SQL/日付・時刻関連/月別の集計を出すの変更点

追加された行はこの色です。
削除された行はこの色です。
PostgreSQL/SQL/日付・時刻関連/月別の集計を出すへ行く。
PostgreSQL/SQL/日付・時刻関連/月別の集計を出すの差分を削除

* 月別の集計を出す [#vba7e530]
** 前提 [#me16661b]
以下のテーブルについて、月別のnumの合計数を出す。
 SELECT * FROM t1;
  num |     dt
 -----+------------
    1 | 2008-01-01
    1 | 2008-01-02
    1 | 2008-02-01
    2 | 2008-04-01
    1 | 2008-06-01
    1 | 2008-06-02
 (6 rows)

** SQL [#dc21a66f]
 SELECT
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 1  THEN num ELSE 0 END) AS DT200801,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 2  THEN num ELSE 0 END) AS DT200802,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 3  THEN num ELSE 0 END) AS DT200803,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 4  THEN num ELSE 0 END) AS DT200804,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 5  THEN num ELSE 0 END) AS DT200805,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 6  THEN num ELSE 0 END) AS DT200806,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 7  THEN num ELSE 0 END) AS DT200807,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 8  THEN num ELSE 0 END) AS DT200808,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 9  THEN num ELSE 0 END) AS DT200809,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 10 THEN num ELSE 0 END) AS DT200810,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 11 THEN num ELSE 0 END) AS DT200811,
       SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 12 THEN num ELSE 0 END) AS DT200812
 FROM  t1
 WHERE dt >= '2008-01-01' AND dt <= '2008-12-31'

** 参考 [#tf4052b8]
別の方法として、ピボットテーブルを使う事も考えられる。