List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 13 1999 12:20am
Subject:Re: IF(), ISNULL(), and COUNT() problem
View as plain text  
On Wed, 1999-08-25 09:38:11 +0900, churo@stripped wrote:

> 1. When tr.pcode is not indexed:
> 
> mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, COUNT(*)) AS count
> FROM tp
> LEFT JOIN tr ON tp.pcode = tr.pcode
> GROUP BY tp.pcode;
> 
> +---------+-------+
> | pcode   | count |
> +---------+-------+
> | kvw2000 |    28 |
> | kvw2001 |     0 |
> +---------+-------+ (as expected)
> 
> 2. When tr.pcode is indexed:
> 
> mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, COUNT(*)) AS count
> FROM tp
> LEFT JOIN tr ON tp.pcode = tr.pcode
> GROUP BY tp.pcode;
> 
> +---------+-------+
> | pcode   | count |
> +---------+-------+
> | kvw2000 |    28 |
> | kvw2001 |     1 |
> +---------+-------+ (not as expected)


I think you actually found an inconsistency in MySQL's extension to
standard SQL, which would forbid the direct use of any fields (in the
SELECT part) not being listed at GROUP BY (usage in group functions
naturally is okay).

But, your query can be done much more direct:

  SELECT tp.pcode, COUNT(tr.pcode)
  FROM   tp LEFT JOIN tr  ON  tp.pcode = tr.pcode
  GROUP BY tp.pcode;

Just COUNT the right column!

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
IF(), ISNULL(), and COUNT() problemchuro25 Aug
  • PHP3 & MySQL simple questionBarry25 Aug
    • Re: PHP3 & MySQL simple questionPaul DuBois25 Aug
      • Re: PHP3 & MySQL simple questionBarry25 Aug
  • IF(), ISNULL(), and COUNT() problemsinisa25 Aug
    • Re: IF(), ISNULL(), and COUNT() problemChuro Park25 Aug
  • Re: IF(), ISNULL(), and COUNT() problemMartin Ramsch13 Sep