MySql count grouping by 3 columns

Question

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

Also, I neglected to mention that all the error status emails would have a date_sent value of null ("0000-00-00 00:00:00') but I do control for this condition with ifnull and using an If statement with the DATE_FORMAT function replacing the NULL value with a valid value (which in this case would be the date the email request was made). — user2895962
– user2895962, Commented Oct 21, 2013 at 17:33
Here is the query with the date_sent value checking included: — user2895962
– user2895962, Commented Oct 21, 2013 at 17:35
IF(DATE_FORMAT(date_sent, '%Y') = '0000', date_entered, date_sent)) AS date_sent, COUNT(IF(DATE_FORMAT(date_sent, '%Y') = '0000', DATE_FORMAT(date_entered, '%Y-%m-%d'), DATE_FORMAT(date_sent, '%Y-%m-%d'))) AS total, SUM(IF(status = 3, 1, 0)) errorcount, SUM(IF(status = 1, 1, 0)) queuecount FROM sendmail GROUP BY DATE_FORMAT(date_sent, '%Y-%m-%d') — user2895962
– user2895962, Commented Oct 21, 2013 at 17:36

Barmar · Accepted Answer · 2013-10-18 19:19:19Z

4

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

answered Oct 18, 2013 at 19:19

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2895962 Over a year ago

Thanks you for your response. I tried both and there was no difference in the results.

Barmar Over a year ago

Can you make a sqlfiddle with sample data?

Collectives™ on Stack Overflow

MySql count grouping by 3 columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related