List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 18 2006 11:54pm
Subject:Re: showing zero counts in select statements with group by
View as plain text  
Sorry, two errors, should be ...

...) OR m.repyear IS NULL
GROUP BY c.year

PB

-----

Chris Fonnesbeck wrote:
> On 2/17/06, Peter Brawley <peter.brawley@stripped> wrote:
>   
>> 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%')
>>
>> group by m.repyear;
>>     
>
> Thanks for the recommendation; unfortunately, it does not work:
>
> mysql> 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.repyear IS NULL
>     -> group by m.repyear;
> +---------+-------+
> | repyear | count |
> +---------+-------+
> |    1994 |     1 |
> |    1996 |   145 |
> |    1997 |    15 |
> |    1999 |    12 |
> |    2000 |    14 |
> |    2001 |    16 |
> |    2002 |    36 |
> |    2003 |    91 |
> |    2004 |     5 |
> |    2005 |    52 |
> +---------+-------+
> 10 rows in set (52.92 sec)
>
> Thanks,
> C.
>
> --
> Chris Fonnesbeck + Atlanta, GA + http://trichech.us
>
>
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/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