SQLで年度別のデータカウントを取得する

仕事で年度別のデータカウントを取得しなければならなかったので、できるだけプログラム(PHP)で処理をしなくていいようにSQLを考えてみた。

※MySQLのみ想定しています。Postgresの場合はpdateの型とdate_formatをto_char(pdate, 'YYYY-04-01 00:00:00')とすれば良いかと。動作保障はできませんので悪しからず。MySQL5.0.45での動作は確認済。

------------------------------------------------------------
年度はX年4月1日～(X+1)年3月31日とする。
例： 2008年4月1日～2009年3月31日は2008年度、 2008年3月31日は2007年度

テスト用テーブル：test
id, pdate(DATETIME型)
1,2008-03-31 00:00:00
2,2008-04-02 00:00:00
3,2007-10-21 00:00:00
4,2007-04-02 00:00:00
5,2007-03-31 00:00:00
6,2006-04-01 00:00:00

上記の年度はそれぞれ 2007, 2008, 2007, 2007, 2006, 2006 となるはず。

【SQL】
SELECT pdate , count(pdate) as yearly_cnt FROM (
SELECT extract(YEAR FROM pdate)-1 as pdate, id FROM test WHERE pdate <>
UNION
SELECT extract(YEAR FROM pdate) as pdate, id FROM test WHERE pdate >= date_format(pdate, '%Y-04-01 00:00:00')
) as tmp_tbl GROUP BY pdate

【実行結果】
pdate yearly_cnt
2006 2
2007 3
2008 1