MySQL Forums
Forum List  »  Newbie

Re: Return 0 for data with no matching values
Posted by: Dean Richert
Date: March 21, 2015 10:26AM

Thanks Peter, I did make some progress after you pointed that out. Unfortunately, I hit another impasse.

At first I made some progress with this query, which seemed to be returning how many dates I have in a year (Since each date in my calendar table is incremented by one hour, I have 365 days x 24 hours = 8760 entries per year):

SELECT YEAR(calendar.dates) AS year,
COUNT YEAR(calendar.dates) AS count
FROM calendar
LEFT JOIN birthdates
ON YEAR(calendar.dates) = YEAR(birthdates.dates)
GROUP BY YEAR(calendar.dates)

Which gave results like this:

Year | count
1969 | 8760
1970 | 8760
1971 | 8760

So I thought I was simply counting the wrong thing. So I tried this query:

SELECT YEAR(calendar.dates) AS year,
COUNT YEAR(birthdates.dates) AS count
FROM calendar
LEFT JOIN birthdates
ON YEAR(calendar.dates) = YEAR(birthdates.dates)
GROUP BY YEAR(calendar.dates)

I am getting results like this:

Year | count

1969 | 0
1970 | 0
1971 | 1699440
1972 | 710471
1973 | 0

I do not have enough birthdays to possibly have over a million in one year, so I am not even sure what is being counted. But the progress was made in that I do have birthdays in 1971 and 1972, but none in 1969, 1970, or 1973 so that was correct.

Any further ideas on what may be going wrong here?

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.