MySQL SUM(), COUNT() and GROUP BY

Question

I have a particular question which is about MySQL. Please have a look at the query and the result set below:

SELECT `SentTo`, 
       `Read`
FROM `Emails` as t

[email protected]   | 1
--------------------------------
[email protected]   | 0
--------------------------------
[email protected]   | 1
--------------------------------
[email protected] | 0
--------------------------------
[email protected] | 1
--------------------------------

I'd like to get the count of the emails for each email, and the total Read. I will get unread by subtracting total and read on the script side, don't really need it here.

Email                | Total | Read
--------------------------------------
[email protected]   |   3   |   1
--------------------------------------
[email protected] |   2   |   1
--------------------------------------

Any advice? I have tried to apply sub-queries to this but I couldn't find a proper solution.

sgeddes · Accepted Answer · 2014-09-23 03:55:29Z

4

This should work using GROUP BY:

SELECT `SentTo`,
       COUNT(*) Total,
       SUM(`Read`=0) 
FROM Emails
GROUP BY `SentTo`

SQL Fiddle Demo

answered Sep 23, 2014 at 3:55

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

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

1 Comment

M Khalid Junaid Over a year ago

SUM(Read=0) A true Mysql solution for conditional count +1

N H · Accepted Answer · 2014-09-23 05:23:03Z

1

A 'Group by' clause can solve this problem as follows:

SELECT `SentTo`, COUNT(*) as totalSend, (totalSend - SUM(`Read`)) as totalUnread
FROM `Emails`  
GROUP BY `SentTo`

edited Sep 23, 2014 at 5:23

answered Sep 23, 2014 at 4:05

N H

337 bronze badges

1 Comment

sgeddes Over a year ago

Besides the fact this isn't valid syntax, it doesn't produce the correct results if it were... First, You can't reference a column alias in the SELECT query like this. Second, COUNT(DISTINCT SentTo) will always equal 1 -- OP is asking for COUNT(*)...

Sadikhasan · Accepted Answer · 2014-09-23 04:17:00Z

0

Group by should give the desired result

SELECT sentTo,
       Sum(READ) AS TotalRead,
       Sum(CASE WHEN READ =0 THEN 1 ELSE 0) AS Unread
FROM Emails
GROUP BY sentTo

edited Sep 23, 2014 at 4:17

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

answered Sep 23, 2014 at 3:54

radar

13.4k2 gold badges27 silver badges33 bronze badges

3 Comments

sgeddes Over a year ago

This would SUM the Read column, not SUM when it's equal to 0. Also, Read is a reserved word in MySql and would need to be escaped.

radar Over a year ago

OP wants values when read is 1, as you can see in the example where first email got 3 reads

sgeddes Over a year ago

That's the Total, which is the COUNT(*). For example, hello has 3 total records, but only 1 with read=0. This query returns hello with 2.

Collectives™ on Stack Overflow

MySQL SUM(), COUNT() and GROUP BY

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related