CODEWITHSUNDEEP
mysql
morissette
morissette

Reputation: 1099

MySQL Group By Counting for Two fields

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)

However I am trying to have each Status for each month:

For example:

May should have a total count of OFI Rejected and a total count of Created OFI. How can I accomplish this?

Upvotes: 0

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think what you are looking for is conditional aggregation:

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);

Upvotes: 0

Rahul
Rahul

Reputation: 77876

There may be a better solution but you can try making 2 separate queries using UNION ALL like below

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));

Upvotes: 1

Related Questions