From: Peter Brawley
Date: February 17 2006 7:35pm
Subject: Re: showing zero counts in select statements with group by
List-Archive: http://lists.mysql.com/mysql/194964
Message-Id: <43F62577.1080009@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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