Count of number of records in each time interval with MySQL

group-by, minute, mysql

Solution

You can group by the intervals and select the counts.

SELECT
    count(date_action) as count,
    CASE 
        WHEN minute(date_action) BETWEEN 0 and 14 THEN '00'
        WHEN minute(date_action) BETWEEN 15 and 29 THEN '15'
        WHEN minute(date_action) BETWEEN 30 and 44 THEN '30'
        WHEN minute(date_action) BETWEEN 45 and 59 THEN '45'
    END AS intervals
FROM
    clients
WHERE
    HOUR(date_action) = 09
    AND DAY(date_action) = 15
    AND MONTH(date_action) = 07
    AND YEAR(date_action) = 2013
    AND rep_id = 28
GROUP BY intervals

Problem

I have a MySQL query which I'm running to work out how many calls have been booked for a user in any given hour (on any given day etc) within 15 minute time intervals. Thus far I have: ``` SELECT date_action, DATE_FORMAT(date_action, "%i") AS minute, CASE WHEN minute(date_action) BETWEEN 0 and 14 THEN '00' WHEN minute(date_action) BETWEEN 15 and 29 THEN '15' WHEN minute(date_action) BETWEEN 30 and 44 THEN '30' WHEN minute(date_action) BETWEEN 45 and 59 THEN '45' END AS intervals FROM clients WHERE HOUR(date_action) = 09 AND DAY(date_action) = 15 AND MONTH(date_action) = 07 AND YEAR(date_action) = 2013 ``` This works well as it is but the magic ingredient I'm missing is a 'count' of the number of calls assigned to each interval, so for example the code above produces: ``` Date_time - Minute - Interval 2013-07-15 09:54:24 - 54 - 45 2013-07-15 09:29:32 - 29 - 15 2013-07-15 09:13:33 - 13 - 00 2013-07-15 09:53:53 - 53 - 45 2013-07-15 09:00:49 - 00 - 00 ``` So, I have my minutes converted into intervals as I only want to return 1 row for each interval and then have appended to it a column with the count of the number of calls in that interval, i.e. ``` Interval - Count 45 - 2 15 - 1 00 - 2 ``` I feel like I've been banging my head against a brick wall on this one for way too long now so any help would be greatly appreciated. Whilst there is some post-processing going on after the query I would much rather do this all in SQL if possible. :)

Original source