Grouping Data by Year -- Return 0 for no results

Question

My Current MySQL query:

SELECT MAX( s.con_grade ) AS max, YEAR( u.sent_date ) AS year
FROM pdb_usersectionmap u
LEFT JOIN pdb_sections s ON u.section_id = s.id
WHERE u.user_id =21
AND YEAR( u.sent_date ) > '2004-01-01'
GROUP BY YEAR( u.sent_date )
ORDER BY u.sent_date ASC

(The year and user_id are generated dynamically in PHP)

I'm trying to display results for the last 5 years. In some circumstances, the user may not have a MAX for that year. For instance, this user only has entries in the last 3 years (but no years before that):

max     year
5     2007
6.05    2008
7     2009

My Question: If I tell MySQL to look for entries in specific years, is there any way for MySQL to return "0" for the year if there are no entries found?

Ideally, I'd love for the output to be (would save me a lot of time):

year    max
2005      0
2006       0
2007      5
2008       6.05
2009       7

Answer 1

isnull(<field>, <value>)

Let me give you an example.

SELECT ISNULL(MAX(s.con_grade),0) AS [max]
      ,YEAR( u.sent_date ) AS [year]
  FROM pdb_usersectionmap u,
      ,pdb_sections s
 WHERE u.section_id = s.id
   AND u.user_id = 21
   AND YEAR(u.sent_date) > '2004-01-01'
 ORDER
    BY u.sent_date

I made that join implicit out of laziness.

Answer 2

Trying sneaking-in this, to make sure you have at least one record for each yeah:

UNION SELECT '0', '2006'
UNION SELECT '0', '2007'
etc..

I'd still try and move this logic into your PHP application.

Answer 3

One correct way of doing this would be having a years table, doing a outer join to it, and grouping by the year column in that table, something like this (not tested)

SELECT MAX( s.con_grade ) AS max, YEAR( u.sent_date ) AS year
FROM pdb_usersectionmap u
LEFT JOIN pdb_sections s ON u.section_id = s.id
RIGHT JOIN years y ON year = y.year_id
WHERE u.user_id =21
AND y.year_id > 2004
GROUP BY y.year_id
ORDER BY u.sent_date ASC

The years table of course could be generated on the fly or by a "distinct" subquery to another table.