Count messages per day BEFORE and AFTER certain time and show in one resultset

mysql, sql

Solution

Try this:

SELECT 
    user_id, 
    _date, 
    SUM(_time <= '16:00') AS before16, 
    SUM(_time > '16:00') AS after16 
FROM messages 
GROUP BY user_id, _date
ORDER BY user_id, _date ASC

Problem

I have the following table with messages: ``` +---------+---------+------------+----------+ | msg_id | user_id | _date | _time | +-------------------+------------+----------+ | 1 | 1 | 2011-01-22 | 06:23:11 | | 2 | 1 | 2011-01-23 | 16:17:03 | | 3 | 1 | 2011-01-23 | 17:05:45 | | 4 | 2 | 2011-01-22 | 23:58:13 | | 5 | 2 | 2011-01-23 | 23:59:32 | | 6 | 3 | 2011-01-22 | 13:45:00 | | 7 | 3 | 2011-01-23 | 13:22:34 | | 8 | 3 | 2011-01-23 | 18:22:34 | +---------+---------+------------+----------+ ``` What I want is for each day, to see how many messages each user has sent BEFORE and AFTER 16:00. I now do this in two steps: ``` SELECT user_id, _date, COUNT(msg_id) AS cnt FROM messages WHERE _time <= '16:00' GROUP BY user_id, _date ORDER BY user_id, _date ASC user_id _date cnt ----------------------------- 1 2011-01-22 1 1 2011-01-23 0 2 2011-01-22 0 2 2011-01-23 0 3 2011-01-22 1 3 2011-01-23 1 SELECT user_id, _date, COUNT(msg_id) AS cnt FROM messages WHERE _time > '16:00' GROUP BY user_id, _date ORDER BY user_id, _date ASC user_id _date cnt ----------------------------- 1 2011-01-22 0 1 2011-01-23 2 2 2011-01-22 1 2 2011-01-23 1 3 2011-01-22 0 3 2011-01-23 1 ``` (In reality, btw, the rows with "0" value aren't given in the resultset. I just added them for clarification) I would like to combine these two outputs into one: ``` user_id _date before16 after16 ------------------------------------- 1 2011-01-22 1 0 1 2011-01-23 0 2 2 2011-01-22 0 1 2 2011-01-23 0 1 3 2011-01-22 1 0 3 2011-01-23 1 1 ``` However, I have no idea on how to write this query. If you do, your help would be appreciated :-)

Original source