按日期列出的一列中不同值的SQL计数

I have a table that looks like:

我有一张表看起来像:

os         date
--         ---
ios        2014-04-35 21:33:33 
android    2014-04-35 21:33:33
ios        2014-04-35 21:33:33
ios        2014-04-35 21:33:33

I want to get an output to be the counts of ios/android in each month. So like:

我想得到一个输出是每个月的ios / android计数。所以喜欢:

Year    Month    ios     android
2015    01       20      100
2015    02       400     20

Something like that.

这样的事情。

This is what I have right now. It's not quite right though:

这就是我现在所拥有的。虽然不太对劲:

SELECT year(last_modified_date) as cy
     , month(last_modified_date) as cm
     , (SELECT COUNT(*) FROM device_info WHERE os='ios')
     , (SELECT COUNT(*) FROM device_info WHERE os='android')
FROM device_info
WHERE year(last_modified_date) IN (2014,2015)
GROUP BY year(last_modified_date)
       , month(last_modified_date);

2 个解决方案

#1

Tru this:

SELECT YEAR(di.date) AS 'Year', 
       MONTH(di.date) AS 'Month', 
       SUM(di.os = 'ios') AS 'ios', 
       SUM(di.os = 'android') AS 'android'
FROM device_info AS di
GROUP BY YEAR(di.date), MONTH(di.date);

#2

You can use conditional aggregation with case expression:

您可以对条件表达式使用条件聚合:

select year(`last_modified_date`) as `Year`
     , month(`last_modified_date`) as `Month`
     , sum(`os` = 'ios') as `ios`
     , sum(`os` = 'android') as `android`
from `device_info`
where year(`last_modified_date`) in (2014, 2015)
group by year(`last_modified_date`)
       , month(`last_modified_date`);