List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 13 2002 9:43pm
Subject:Re: Query help; temporary table?
View as plain text  
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

Thread
Query help; temporary table?btt13 Sep
  • Re: Query help; temporary table?Roger Baklund13 Sep
Re: Query help; temporary table?btt14 Sep
Re: Query help; temporary table?Paul DuBois14 Sep
  • Does mySQL have cross-reference (AKA pivot) tables support ?Mauricio Sthandier R.14 Sep
    • Re: Does mySQL have cross-reference (AKA pivot) tables support ?Paul DuBois14 Sep
  • Re: Does mySQL have cross-reference (AKA pivot) tables support ?Mauricio Sthandier R.14 Sep