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
