title: How to get data grouped by hour in mysql
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>