Return 0 for data with no matching values
I am trying to group data over a period of time but I cannot seem to find out how to insert values of 0 for where no data exists.
Here is a sample query:
SELECT YEAR(birthday) AS year, MONTH(birthday) AS month, HOUR(birthday) AS hour, COUNT(*) AS count FROM birthdates GROUP BY HOUR(birthday), MONTH(birthday), YEAR(birthday) ORDER BY year ASC, month ASC, hour ASC
I want entries without any data, for example, an hour or month in which there is no birthday, to return 0 in my query. But presently, the query only returns data with non-zero values.
How can I solve this? I thought about using a left join on a table that has every possible time entry in it that I compare against, but I don’t believe such a table could be feasibly maintained, as I would need a new entry at least every hour and I would have to guarantee that entry exists before data comes in that might use that entry. I could make enough entries for the foreseeable future (such as all entries needed for the next 2 years), but this would mess up my data by returning too many zero data, including data about the future. (Every date past the present would have 0’s in it).
What is the best solution to implement here? I thought I was on the wrong track with my left join idea.