两个字段的计数MySQL组

Question

mysql> SELECT date_format(FROM_UNIXTIME(timestamp), '%M') AS month,   
       YEAR(FROM_UNIXTIME(timestamp)) as year, left(content_value, 12) AS status, 
       count(*) AS count FROM gg_groom_content 
       WHERE content_value LIKE '%created ofi%' OR 
       content_value LIKE '%ofi rejected%' 
       GROUP BY MONTH(from_unixtime(timestamp)), YEAR(from_unixtime(timestamp));

Result:
+-----------+------+--------------+-------+
| month     | year | status       | count |
+-----------+------+--------------+-------+
| January   | 2014 | OFI Rejected |   861 |
| February  | 2014 | Created OFI: |   777 |
| March     | 2014 | Created OFI: |   537 |
| April     | 2014 | OFI Rejected |   285 |
| May       | 2014 | OFI Rejected |   198 |
| September | 2011 | (06:32:40 PM |     1 |
| November  | 2013 | Created OFI: |    86 |
| December  | 2013 | Created OFI: |   561 |
+-----------+------+--------------+-------+
8 rows in set (0.91 sec)

然而，我试着每个月都有自己的状态：

例如：

五月应该有一个总数量的OFI拒绝和总数量的创建OFI。我怎样才能做到这一点？

Answer 1

也许有更好的解决方案，但您可以尝试使用UNION ALL进行两个单独的查询，如下所示

SELECT date_format(FROM_UNIXTIME(timestamp), '%M') AS month,   
YEAR(FROM_UNIXTIME(timestamp)) as year, left(content_value, 12) AS status, 
count(*) AS count FROM gg_groom_content 
WHERE content_value LIKE '%created ofi%'
GROUP BY MONTH(from_unixtime(timestamp)), YEAR(from_unixtime(timestamp));

UNION ALL

SELECT date_format(FROM_UNIXTIME(timestamp), '%M') AS month,   
YEAR(FROM_UNIXTIME(timestamp)) as year, left(content_value, 12) AS status, 
count(*) AS count FROM gg_groom_content 
WHERE content_value LIKE '%ofi rejected%' 
GROUP BY MONTH(from_unixtime(timestamp)), YEAR(from_unixtime(timestamp));

Answer 2

我认为您要寻找的是有条件的聚合：

SELECT date_format(FROM_UNIXTIME(timestamp), '%M') AS month,   
       YEAR(FROM_UNIXTIME(timestamp)) as year, left(content_value, 12) AS status, 
       sum(content_value LIKE '%created ofi%') as CreatedOFI,
       sum(content_value LIKE '%ofi rejected%') as RejectedOFI
FROM gg_groom_content 
WHERE content_value LIKE '%created ofi%' OR 
      content_value LIKE '%ofi rejected%' 
GROUP BY MONTH(from_unixtime(timestamp)), YEAR(from_unixtime(timestamp))
ORDER BY MIN(timestamp);