月別の集計を出す

前提

以下のテーブルについて、月別の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

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'

参考

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


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS

Last-modified: 2009-10-29 (木) 18:37:22