Join two group by commands into one
I have a table
________________________________________________________________________________ | Message | |_______________________________________________________________________________| | ID(INT) | Text(TEXT) |read(TINYINT(0/1)) |deleted(TINYINT(0/1))| User_id | | 1 | How Are You?| 0 | 0 | 6 | | 2 | Fine | 0 | 1 | 4 | | 3 | Message 3 | 1 | 0 | 6 | | 4 | Message 4 | 0 | 1 | 6 | | 5 | Message 5 | 1 | 0 | 5 | |_________|_____________|___________________|_____________________|_____________|
Now I want to select the message where user_id=6 and also select the column count for read=0 and read=1 seperately.I know this can be done by group by command and iam currently doing it with two sql queries .May somebody join them in one
select message,count(*) from message where User_id=6 and read=0 group by id;//for unread message select message,count(*) from message where User_id=6 and read=1 group by id;//for read message
Why don't you
select message, sum(read) as num_read, sum(case read when 0 then 1 else 0 end) as num_unread from message where user_id = 6 group by user_id
? You can drop either the where or the group by line, depending on your real need and SQL implementation.
Joining your two queries is quite easy, but I don't get what you really want.
SELECT `message`, count(*) AS `cnt` , `read` FROM message WHERE `User_id`=6 AND `read` IN ( 0, 1 ) GROUP BY `id` , `read`;
select read,id,count(*) from message where user_id = 6 and read in (0,1) group by read,id