At 17:31 -0400 9/13/02, btt@stripped wrote:
> On Fri, Sep 13, 2002 at 04:16:53PM +0200, Roger Baklund wrote:
> > Move all criteria related to 'dates' to the ON clause:
> >
> > SELECT c.id, c.name,count(*) AS count
> > FROM counties c
> > LEFT JOIN dates d ON
> > d.county = c.id AND
> > DATE_FORMAT(d.date, '%m%Y') = '${month}${year}'
> > WHERE
> > c.id < 99
> > GROUP BY c.id
> > ORDER BY c.name
>
> That is very interesting... Thanks for the tip. It works great except
> that if I use count(*) everything that should be zero is one, and
> anything that is greater than zero is correct. Using count(d.date)
> fixes this. Why the count(*) returns 1s instead of 0s is a little
> strange.
Actually, it's not. COUNT(*) counts rows, regardless of their content.
COUNT(expr) counts only non-NULL values.
sql,query
