List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:February 17 2006 7:38pm
Subject:Re: showing zero counts in select statements with group by
View as plain text  
In the last episode (Feb 17), Chris Fonnesbeck said:
> 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:
> 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;

You probably want to group by y.year here, since for any year not in
your mortality table, m.year will be null (thus grouping all your
unused years together).  Also, select "count(m.repyear)" (or any other
field in m) instead of "count(*)", since "*" includes nulls (and would
cause all your unused years to have a count of 1.

If you remove the group by clause and look at the raw table generated
by the left join it may make more sense.

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

There's no way for mysql to have guessed that you wanted to see
nonexistent values in your resultset.  Since the query is only looking
at a subset of the table, how would you even know which values were
missing?  Should it extend that number past 2005 to whatever the
field's maximum value is?  I assume it's just an integer field, so
mysql couldn't have known it was a field storing a number known to be
between 1986 and 2006.

	Dan Nelson
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