Jens, You need to select on the games on the left, to ensure that you don't end up with years on the left with no corresponding game records. Try this:
SELECT years.year AS yearlist, count(years.year) AS gamecount FROM games LEFT JOIN years ON games.release_year = years.year GROUP BY years.year ORDER BY gamecount DESC, yearlist Or just simply SELECT release_year AS yearlist, count(release_year) AS gamecount FROM games GROUP BY release_year ORDER BY gamecount DESC, yearlist (Not tested here, sorry ...) Tom Haapanen [EMAIL PROTECTED] -----Original Message----- From: Jens Mildner [mailto:[EMAIL PROTECTED]] Sent: Monday, 07 January, 2002 15:08 To: [EMAIL PROTECTED] Subject: Count() does not count correctly? Hello MySQL-users ! Houston, I've got a problem here. I'm running the following query from a PHP-Script: SELECT years.year AS yearlist, count(years.year) AS gamecount FROM years LEFT JOIN games ON years.year = games.release_year GROUP BY years.year ORDER BY gamecount DESC, yearlist This query should do the following: Every year from years.year should be returned along with the number of games that have been released in that year sorted by the gamecount. years.year and games.release_year both do NOT contain NULL. The problem is the following: Years in which no games were released are returned with a gamecount of 1. Why that??? All other years with games released in it are getting the correct gamecount. Please help me. Greetings from Germany, Jens Mildner. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
