Grouping data by year - return 0 with no results
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
(Year and user_id are generated dynamically in PHP)
I have been trying to show results for the past 5 years. In some cases, the user may not have MAX this year. For example, this user only has records from the last 3 years (but several years before):
max year
5 2007
6.05 2008
7 2009
My question is: If I tell MySQL to search for records in specific years, is there a way for MySQL to return "0" for a year if there are no records?
Ideally, I would like the output to be (it will take me a very long time):
year max
2005 0
2006 0
2007 5
2008 6.05
2009 7
a source to share
One correct way to do this is to have a table of years, do an outer join to it, and group 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 can, of course, be generated on the fly or in a separate subquery into another table.
a source to share
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 this connection implicit out of laziness.
a source to share