From: Peter Brawley Date: February 18 2006 11:54pm Subject: Re: showing zero counts in select statements with group by List-Archive: http://lists.mysql.com/mysql/194972 Message-Id: <43F7B3CB.8050800@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-43F7B3CB5992=======" --=======AVGMAIL-43F7B3CB5992======= Content-Type: multipart/alternative; boundary=------------020303020202020309050500 --------------020303020202020309050500 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Sorry, two errors, should be ... ...) OR m.repyear IS NULL GROUP BY c.year PB ----- Chris Fonnesbeck wrote: > On 2/17/06, Peter Brawley 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 > > > --------------020303020202020309050500 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
--------------020303020202020309050500-- --=======AVGMAIL-43F7B3CB5992======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-43F7B3CB5992=======--