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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
