Re: Return 0 for data with no matching values
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?