You might try changing it to distinct if you are looking for unique count of
ids from each.
SELECT a.a,aa,COUNT(DISTINCT b.id),COUNT(DISTINCT c.id) FROM...
Since you are doing a left join, there always going to be something for b.id and c.id, even if the "value" is NULL. Distinct may
work to filter out duplicates like NULL. Otherwise you'll need to do it long hand:
SELECT a.a,aa,COUNT(IF(b.id IS NULL,0,1)),COUNT(IF(c.id IS NULL,0,1)) FROM...
----- Original Message -----
From: "Jørn Dahl-Stamnes" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Monday, September 18, 2006 6:39 AM
Subject: COUNT question
I have a query like:
SELECT a.a,aa,COUNT(b.id),COUNT(c.id) FROM a LEFT JOIN b ON (b.a_ref=a.id)
LEFT JOIN c ON (c.a_ref=a.id);
But it seems like SQL is mixing up the two count's. I get the count from table
c instead of table b for the first occurence of COUNT in the query.
Can I use two COUNT's in one query or do I have to split the query into two
queries?
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
