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