CODEWITHSUNDEEP
mysqlsql
ldrrp
ldrrp

Reputation: 704

MySQL SUM(), COUNT() and GROUP BY

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.

Upvotes: 0

Views: 1998

Answers (3)

N H
N H

Reputation: 33

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`

Upvotes: 1

radar
radar

Reputation: 13425

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

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

This should work using GROUP BY:

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

Upvotes: 4

Related Questions