Yanor.net/
Wiki
Blog
GitHub
Sandbox
開始行:
* 月別の集計を出す [#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...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 2 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 3 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 4 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 5 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 6 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 7 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 8 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 9 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 10 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 11 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 12 THEN num...
FROM t1
WHERE dt >= '2008-01-01' AND dt <= '2008-12-31'
** 参考 [#tf4052b8]
別の方法として、ピボットテーブルを使う事も考えられる。
終了行:
* 月別の集計を出す [#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...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 2 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 3 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 4 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 5 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 6 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 7 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 8 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 9 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 10 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 11 THEN num...
SUM(CASE WHEN EXTRACT(MONTH FROM dt) = 12 THEN num...
FROM t1
WHERE dt >= '2008-01-01' AND dt <= '2008-12-31'
** 参考 [#tf4052b8]
別の方法として、ピボットテーブルを使う事も考えられる。
ページ名: