Tutorial :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  


Solution:1

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.


Solution: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.


Solution:3

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »