I am trying to figure out how to get a comprehensive count of rows in a database by year. Unfortunately, when there are no rows for a particular year, the year is omitted from the result set, rather than showing up with a zero count:
mysql> select count(*) as count, repyear from mortality where region like 'Southwest' and repyear>1985 and dthcode!=4 and (cause like '%red tide%' or remarks like '%red tide%') group by repyear; +-------+---------+ | count | repyear | +-------+---------+ | 1 | 1994 | | 145 | 1996 | | 15 | 1997 | | 12 | 1999 | | 14 | 2000 | | 16 | 2001 | | 36 | 2002 | | 91 | 2003 | | 5 | 2004 | | 52 | 2005 | +-------+---------+ 10 rows in set (0.09 sec) Looking at some online help, I understand that I am supposed to create a table with the years as entries, then run a left join with the same query in order to get my zeroes included. I went ahead and did this: mysql> select * from years; +------+ | year | +------+ | 1986 | | 1987 | | 1988 | | 1989 | | 1990 | | 1991 | | 1992 | | 1993 | | 1994 | | 1995 | | 1996 | | 1997 | | 1998 | | 1999 | | 2000 | | 2001 | | 2002 | | 2003 | | 2004 | | 2005 | +------+ 20 rows in set (0.00 sec) However, running a left joined query using this table gives the same result: mysql> select count(*) as count, m.repyear as repyear from years y left join mortality m on y.year=m.repyear where m.region like 'Southwest' and m.repyear>1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') group by m.repyear; +-------+---------+ | count | repyear | +-------+---------+ | 1 | 1994 | | 145 | 1996 | | 15 | 1997 | | 12 | 1999 | | 14 | 2000 | | 16 | 2001 | | 36 | 2002 | | 91 | 2003 | | 5 | 2004 | | 52 | 2005 | +-------+---------+ 10 rows in set (0.12 sec) So, I'm at a loss as to how to get a complete result set. I do find it a bit strange that sql returns an incomplete query as a default. Any ideas most welcome. -- Chris Fonnesbeck + Atlanta, GA + http://trichech.us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]