MySQL Forums
Forum List  »  Newbie

Return 0 for data with no matching values
Posted by: Dean Richert
Date: March 19, 2015 12:54PM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.