MySQL Group By Hours

by

Your existing query can be reduced to:

SELECT   CONCAT(HOUR(created), ':00-', HOUR(created)+1, ':00') AS Hours
  ,      COUNT(*) AS `usage`
FROM     history
WHERE    created BETWEEN '2012-02-07' AND NOW()
GROUP BY HOUR(created)

To display every hour, including those for which there is no data, you need to outer join with a table containing all the hours for which you want data. You can build such a table in your query using UNION:

SELECT   CONCAT(Hour, ':00-', Hour+1, ':00') AS Hours
  ,      COUNT(created) AS `usage`
FROM     history
  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) = Hour
WHERE    created BETWEEN '2012-02-07' AND NOW() OR created IS NULL
GROUP BY Hour
ORDER BY Hour

However, the treatment of groups for which no data exists is really a matter for business logic that’s best placed in your data access layer rather than in the database itself: indeed it should be trivial for your application to use a zero value whenever an hour is absent.

More Related Contents:

  1. Using LIMIT within GROUP BY to get N results per group?
  2. Error related to only_full_group_by when executing a query in MySql
  3. MySQL Query GROUP BY day / month / year
  4. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  5. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  6. Grouping into interval of 5 minutes within a time range
  7. Retrieving the last record in each group – MySQL
  8. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  9. Mysql query to dynamically convert rows to columns
  10. MySQL “Group By” and “Order By”
  11. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  12. Group mysql query by 15 min intervals
  13. In SQL, how to select the top 2 rows for each group
  14. SQL Query to get column values that correspond with MAX value of another column?
  15. MySQL Group By and Sum total value of other column
  16. Is it possible to GROUP BY multiple columns using MySQL?
  17. Using Count to find the number of occurrences
  18. What does SQL clause “GROUP BY 1” mean?
  19. Using ORDER BY and GROUP BY together
  20. MySQL: How do I join same table multiple times?
  21. GROUP BY – do not group NULL
  22. MySQL GROUP BY DateTime +/- 3 seconds
  23. mysql count group by having
  24. SQL query with avg and group by
  25. Delete the entire content of all table in a Database [closed]
  26. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  27. Mysql Convert Column to row (Pivot table )
  28. Merge two tables in one SQL query and make the date values unique
  29. Greatest value of multiple columns with column name?
  30. Optimistic locking in MySQL

Leave a Comment