SQL文で困っています。

Question

SQL文で以下の事がやりたいです。
まず、table
+------+------------+------------+------------+------------+---------------------+
| id | interview | decision | accept | ng | created_at |
+------+------------+------------+------------+------------+---------------------+
| 5 | 2016-06-09 | 2016-06-10 | 2016-06-11 | 2016-06-12 | 2016-09-02 00:00:00 |
| 5 | 2016-06-09 | 2016-07-10 | 2016-07-11 | 2016-07-12 | 2016-09-02 00:00:00 |
| 5 | 2016-08-09 | 2016-08-10 | 2016-08-11 | 2016-08-12 | 2016-09-02 00:00:00 |
| 5 | 2016-09-09 | 2016-08-10 | 2016-09-11 | 2016-09-12 | 2016-09-02 00:00:00 |
| 5 | 2016-10-09 | 2016-10-10 | 2016-10-11 | 2016-10-12 | 2016-09-02 00:00:00 |
| 5 | 2016-11-09 | 2016-11-10 | 2016-11-11 | 2016-11-12 | 2016-09-02 00:00:00 |
+------+------------+------------+------------+------------+---------------------+

ある期間を月毎にまとめて集計し、各項目の集計もしたいです。
例えば2016-03 - 201612
03は ０、０、０、０が並び、
06は ２、１、１、１が表示する事は
不可能でしょうか。
※カラムの並びは
interview,decision,accept,ng

Answer 1

既に解決済ですが、
以下のようにケース式とSUM関数の合わせ技ですっきり書けるかも。

ただしデカルト積(直積結合)を利用しているため、
データ量が増えるとパフォーマンス的には不利となるかもしれません。

SELECT
    cal.month
,   SUM(CASE WHEN cal.month = DATE_FORMAT(t.interview,'%Y%m') THEN 1 ELSE 0 END) AS interview_cnt
,   SUM(CASE WHEN cal.month = DATE_FORMAT(t.decision,'%Y%m') THEN 1 ELSE 0 END) AS decision_cnt
,   SUM(CASE WHEN cal.month = DATE_FORMAT(t.accept,'%Y%m') THEN 1 ELSE 0 END) AS accept_cnt
,   SUM(CASE WHEN cal.month = DATE_FORMAT(t.ng,'%Y%m') THEN 1 ELSE 0 END) AS ng_cnt
FROM
(
    SELECT '201603' AS "month"
    UNION ALL SELECT '201604'
    UNION ALL SELECT '201605'
    UNION ALL SELECT '201606'
    UNION ALL SELECT '201607'
    UNION ALL SELECT '201608'
    UNION ALL SELECT '201609'
    UNION ALL SELECT '201610'
    UNION ALL SELECT '201611'
    UNION ALL SELECT '201612'

) cal
CROSS JOIN tbl t
GROUP BY
    cal.month
ORDER BY
    cal.month

8月度が漏れてました・・・

Answer 2

ぱっと思いつくのはunionで無理やり

• 元データ

create table tbl (interview date ,decision date ,accept date, ng date);
insert into tbl values
('2016-06-09','2016-06-10','2016-06-11','2016-06-12'),
('2016-06-09','2016-07-10','2016-07-11','2016-07-12'),
('2016-08-09','2016-08-10','2016-08-11','2016-08-12'),
('2016-09-09','2016-08-10','2016-09-11','2016-09-12'),
('2016-10-09','2016-10-10','2016-10-11','2016-10-12'),
('2016-11-09','2016-11-10','2016-11-11','2016-11-12');

• 集計

select month
,sum((cap='interview')*c) as c1
,sum((cap='decision')*c) as c2
,sum((cap='accept')*c) as c3
,sum((cap='ng')*c) as c4
 from
(select DATE_FORMAT(interview,'%Y%m') as month,'interview' as cap,count(*) as c from tbl group by month
union  select DATE_FORMAT(decision,'%Y%m') as month,'decision',count(*) from tbl group by month
union  select DATE_FORMAT(accept,'%Y%m') as month,'accept',count(*) from tbl group by month
union select DATE_FORMAT(ng,'%Y%m') as month,'ng',count(*) from tbl group by month
union select '201603','dummy',0
union select '201604','dummy',0
union select '201605','dummy',0
union select '201606','dummy',0
union select '201607','dummy',0
union select '201608','dummy',0
union select '201609','dummy',0
union select '201610','dummy',0
union select '201611','dummy',0
union select '201612','dummy',0
) as dummy
group by month;

なんか効率的な方法がありそうな気がしますが・・・

• 別案

select month
,sum(c1) as c1
,sum(c2) as c2
,sum(c3) as c3
,sum(c4) as c4
 from
(select DATE_FORMAT(interview,'%Y%m') as month,count(*) as c1,0 as c2,0 as c3,0 as c4 from tbl group by month
union  select DATE_FORMAT(decision,'%Y%m') as month,0,count(*),0,0 from tbl group by month
union  select DATE_FORMAT(accept,'%Y%m') as month,0,0,count(*),0 from tbl group by month
union select DATE_FORMAT(ng,'%Y%m') as month,0,0,0,count(*) from tbl group by month
union select '201603',0,0,0,0
union select '201604',0,0,0,0
union select '201605',0,0,0,0
union select '201606',0,0,0,0
union select '201607',0,0,0,0
union select '201608',0,0,0,0
union select '201609',0,0,0,0
union select '201610',0,0,0,0
union select '201611',0,0,0,0
union select '201612',0,0,0,0
) as dummy            
group by month;