MySQL Forums
Forum List  »  Newbie

Re: Return 0 for data with no matching values
Posted by: Dean Richert
Date: March 23, 2015 09:58AM

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.

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.