I have table as below :-
With help of MySql query, I want result as below:
date total read unread
2018-01-31 8 4 4
2018-02-01 2 2 0
Add a comment
|
4 Answers
Try this:
SELECT date, COUNT(*) as total,
SUM(CASE WHEN read = 1 THEN 1 ELSE 0) as read
SUM(CASE WHEN read = 0 THEN 1 ELSE 0) as unread
FROM yourtable
GROUP BY date
6 Comments
Tarika
On using this query "SELECT
id,date, COUNT(*) as totalcnt, SUM(read) as readcnt, SUM(CASE WHEN read = '0' THEN 1 ELSE 0) as unreadcnt FROM email_notifications GROUP BY date" 1064 error is coming
Joe Taras
@Tarika: dear, remove id field. Because it doesn't present in group by
Shushil Bohara
Or you can use aggregate function on
id as MAX(id) as idTarika
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as unreadcnt FROM email_notifications GROUP BY
date,id LIMIT 0, 30' at line 3Joe Taras
@Tarika:you have missed END statement after ELSE 0
|
you can use case for aggregate the filtered value for read
select date
, count(*), sum(case when read=1 then 1 else 0 end ) as read
, sum(case when read=0 then 1 else 0 end ) as unread
from my_table
group by date
Comments
Try This we don't need case in the read column as 1 means read so we simply sum the value. It will help in the performance if we are dealing with the huge data:
SELECT date,
COUNT(*) as total,
SUM(read) as read --Case not needed here
SUM(CASE WHEN read = 0 THEN 1 ELSE 0) as unread
FROM yourtable
GROUP BY date
Comments
You have to group rows by date using group by, then you can use count to count the total number of rows within each date group (= total column).
In order to obtain the number of read and unread, you can sum read and unread value
SELECT date
, COUNT(*) AS total
, SUM(READ) AS READ
, SUM(CASE WHEN READ = 0 THEN 1 ELSE 0 END) AS unread
FROM mytable
GROUP BY date
