MySQL count() problem

Question

Setup:

create table main(id integer unsigned);
create table test1(id integer unsigned);
create table test2(id integer unsigned);

insert into main(id) value(1);
insert into test1(id) value(1);
insert into test1(id) value(1);
insert into test2(id) value(1);    
insert into test2(id) value(1);
insert into test2(id) value(1);

Using:

   select main.id, 
          count(test1.id),
          count(test2.id) 
     from main
left join test1 on main.id=test1.id
left join test2 on main.id=test2.id
group by main.id;

...returns:

+------+-----------------+-----------------+
| id   | count(test1.id) | count(test2.id) |
+------+-----------------+-----------------+
|    1 |               6 |               6 |
+------+-----------------+-----------------+

How to get the desired result of 1 2 3?

EDIT

The solution should be extensible,I'm going to query multiple count() information about main.id in the future.

Answer 1

Not optimal, but works:

select 
    count(*),
    (select count(*) from test1 where test1.id = main.id) as test1_count,
    (select count(*) from test2 where test2.id = main.id) as test2_count
from main

Answer 2

Not sure if this works in MySQL exactly as written (I'm using Oracle):

  1  select main.id, t1.rowcount, t2.rowcount
  2  from main
  3    left join (select id,count(*) rowcount from test1 group by id) t1
  4              on t1.id = main.id
  5    left join (select id,count(*) rowcount from test2 group by id) t2
  6*             on t2.id = main.id

SQL> /

    ID   ROWCOUNT   ROWCOUNT

---

     1          2          3

Answer 3

You created tables that contain the following:

Table main

id
----
1

Table test1

id
----
1
1

Table test2

id
----
1
1
1

When you join this like you do you will get the following

id  id  id 
-----------
1   1   1
1   1   1
1   1   1
1   1   1
1   1   1
1   1   1

So how should SQL answer differently?

You can call:

SELECT id,COUNT(id) FROM main GROUP BY id

for every table, then join them by id.

Answer 4

You're inadvertently creating a Cartesian product between test1 and test2, so every matching row in test1 is combined with every matching row in test2. The result of both counts, therefore, is the count of matching rows in test1 multiplied by the count of matching rows in test2.

This is a common SQL antipattern. A lot of people have this problem, because they think they have to get both counts in a single query.

Some other folks on this thread have suggested ways of compensating for the Cartesian product through creative use of subqueries, but the solution is simply to run two separate queries:

select main.id, count(test1.id)
from main
left join test1 on main.id=test1.id
group by main.id;

select main.id, count(test2.id) 
from main
left join test2 on main.id=test2.id
group by main.id;

You don't have to do every task in a single SQL query! Frequently it's easier to code -- and easier for the RDBMS to execute -- multiple simpler queries.

Answer 5

You can get the desired result by using:

SELECT COUNT(*) as main_count, 
(SELECT COUNT(*) FROM table1) as table1Count,
(SELECT COUNT(*) from table2) as table2Count FROM main