CODEWITHSUNDEEP
mysql
Safi Baig
Safi Baig

Reputation: 99

How to get data grouped by hour in mysql

I am trying to build a query to get data count per hour for a particular day(or today). Main issue I am getting is I have to show count for all 24 hours in a day even if data does not exist for that particular hour. At that time it has to show count as zero. As of now I have come up with following query.

SELECT   Hour
  ,      COUNT(created_on) AS `user_count`
FROM     users
  RIGHT JOIN (
                   SELECT  0 AS Hour
         UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3
         UNION ALL SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6
         UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9
         UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
         UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
         UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
         UNION ALL SELECT 22 UNION ALL SELECT 23
  )      AS AllHours ON HOUR(created_on) = Hour
WHERE    created_on BETWEEN '2013-04-26' AND NOW() OR created_on IS NULL
GROUP BY Hour
ORDER BY Hour

Upvotes: 1

Views: 1115

Answers (1)

hjpotter92
hjpotter92

Reputation: 80653

Just change the GROUP BY to:

GROUP BY HOUR(created_on)

And your query shall be:

SELECT   HOUR(created_on) AS Hour,
         COUNT(created_on) AS `user_count`
FROM     users
WHERE    created_on BETWEEN '2013-04-26' AND NOW() OR created_on IS NULL
GROUP BY HOUR(created_on)
ORDER BY Hour

Upvotes: 2

Related Questions