MySQL Forums
Forum List  »  Newbie

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

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.

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.