2014年10月13日月曜日

MySQLで月別クロス集計

CASE式を使う。

select 
    user_id,
    name,
    sum(date_format(created, '%Y-%m') = '2014-06') as '2014-06',
    sum(date_format(created, '%Y-%m') = '2014-07') as '2014-07',
    sum(date_format(created, '%Y-%m') = '2014-08') as '2014-08',
    count(*) total
from
    customers
        inner join
    users ON users.id = customers.user_id
where
    created between '2014-06-01' and '2014-08-01'
group by user_id;

0 件のコメント:

コメントを投稿