CODEWITHSUNDEEP
mysql
user2895962
user2895962

Reputation: 21

MySql count grouping by 3 columns

I want to count how many emails were sent out for each day, how many of the emails that were sent had an "error" status each day and how many had a "queue" status each day.

Here is an example of my table structure:

mail_id (int)
date_sent (datetime)
status (tinyint) (1=queue, 2=sent, 3=error)

Here is my query:

SELECT date_sent, 
       COUNT(date_sent) AS total, 
       SUM(IF(status = 3, 1, 0)) errorcount, 
       SUM(IF(status = 1, 1, 0)) queuecount 
FROM sendmail 
GROUP BY date_sent, status

Here is an example of my results:

date_sent     total errorcount      queuecount
2013-02-11    50       50          10 
2013-02-11     1        0           0
2013-02-11     1        0           0
2013-02-11     1        0           0
2013-02-12     1        0           0
2013-02-12     1        0           0
2013-02-12     1        0           0 
2013-02-12     1        0           0
2013-02-12     1        0           0
2013-02-13     1        0           0
2013-02-13     1        0           0
2013-02-13     1        0           0

For example, if there were a total of 50 errors for the entire time period, all the error status emails were grouped into one date 2013-01-11 instead of being distributed to the dates that they occurred on.

Here's an example of my desired results: 

date_sent   total           errorcount              queuecount
2013-02-11   4                1                       1 
2013-02-12   5                2                       1
2013-02-13   3                1                       1

Any assistance would be greatly appreciated.

This is my first post to any forum as I'm a new web developer so any advice or problems with my posting would be helpful.

Thanks

Upvotes: 2

Views: 64

Answers (1)

Barmar
Barmar

Reputation: 781340

You should only group by date_sent, not by status, since you're counting different statuses in the output columns.

Upvotes: 4

Related Questions