List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 17 2006 7:35pm
Subject:Re: showing zero counts in select statements with group by
View as plain text  
Chris,

Your WHERE clause is weeding out the NULL joined entries. Try something 
like...

select
 m.repyear as repyear,
count(*) as count
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%')
) OR m.year IS NULL
group by m.repyear;

PB

-----

Chris Fonnesbeck wrote:
> 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
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.10/263 - Release Date: 2/16/2006

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