Re: Return 0 for data with no matching values
Thanks for the further help, but unfortunately I could not understand your suggestion this time. I tried various permutations but only received errors. I do not often use abbreviations for tables so I do not know what 'c' meant in your line
"from (select distinct year(date) yr from calendar) c" I thought you might have been referring to the calendar table, but I tried that and received the error:
Error Code: 1109. Unknown table 'calendar' in field list.
So I tried without it:
Error Code: 1248. Every derived table must have its own alias
I also could not follow this segment:
distinct year(date) yr from calendar.
It seemed like you were trying to either refer to a column named ‘yr’, but I don't have one, just a column named ‘dates’ in the calendar table which contains timestamps in in the mysql date format: Y-m-d H:i:s, or 2001-12-31 22:00:00. I as noted before, each timestamp increments by an hour, so the next row will contain 2001-12-31 23:00:00, and so on.
calendar table example:
id | dates
0 2001-12-31 21:00:00
1 2001-12-31 22:00:00
2 2001-12-31 23:00:00
So I thought you were maybe referring to the DATE() function and meant YEAR(DATE(calendar.dates) from calendar, so I substituted that to get
SELECT YEAR(calendar.dates) AS year,
COUNT YEAR(calendar.dates) AS count
FROM (SELECT DISTINCT YEAR(DATE(calendar.dates)) FROM calendar)
LEFT JOIN birthdates
ON YEAR(calendar.dates) = YEAR(birthdates.dates)
GROUP BY YEAR(calendar.dates)
but again received:
Error Code: 1248. Every derived table must have its own alias.
I don't know if you are using shorthand or leaving things out that you might expect me to know, as I have not done this before, but if you could be as explicit as possible, it would likely help me get this faster. Just assume I don't know because this aspect of mysql is pretty new to me. I mostly do simple CRUDs. I've tried following as many tutorials on making a calendar table (even from other database platforms) but they are more simple queries than what I am doing here and I encounter other errors on the way. The difference I have found is that each example tutorial only has a date for year, rather than having a full timestamp, so maybe that is having an impact here and why I am getting results of dates x 8760. But regardless I appreciate your attempts so far to help me.