List:General Discussion« Previous MessageNext Message »
From:Chris Fonnesbeck Date:February 17 2006 7:10pm
Subject:showing zero counts in select statements with group by
View as plain text  
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
Thread
showing zero counts in select statements with group byChris Fonnesbeck17 Feb
  • Re: showing zero counts in select statements with group byPeter Brawley17 Feb
  • Re: showing zero counts in select statements with group byDan Nelson17 Feb
Re: showing zero counts in select statements with group byPeter Brawley19 Feb