Re: Return 0 for data with no matching values
Here are the create table statements:
CREATE TABLE `birthdates` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`birthday` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE `calendar` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dates` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Here is some sample data:
INSERT INTO `birthdates` VALUES (1,'2001-10-25 15:45:00'),(2,'2001-10-25 15:45:00'),(3,'2001-11-25 11:45:00'),(4,'2001-12-25 12:45:00'),(5,'2002-10-25 13:45:00'),(6,'2002-11-25 14:45:00'),(7,'2002-12-25 15:45:00'),(8,'2005-05-25 16:45:00'),(9,'2005-06-25 20:45:00'),(10,'2005-07-25 20:45:00');
INSERT INTO `calendar` VALUES (1,'2001-12-20 22:00:00'),(2,'2001-12-20 23:00:00'),(3,'2001-12-21 00:00:00'),(4,'2002-12-20 15:00:00'),(5,'2003-12-20 14:00:00'),(6,'2004-12-20 12:00:00'),(7,'2005-12-20 23:00:00'),(8,'2006-12-20 23:00:00'),(9,'2006-12-21 15:00:00'),(10,'2006-12-22 14:00:00'),(11,'2006-12-23 01:00:00'),(12,'2008-12-23 23:00:00'),(13,'2009-12-23 23:00:00'),(14,'2010-12-23 23:00:00');
This is just sample data. I can attempt to provide the sql for my real calender table, but note that it took me over an hour to create the table due to having over 100,000 entries. In my actual calendar table, there is a datetime entry for every hour on the hour.
Running this query on the sample data:
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)
yields a result showing that the birthdate.dates count for the year 2001 is 12, but there are only actually 10 total dates in the birthdates.dates column.