CODEWITHSUNDEEP
mysqlsql
Bill
Bill

Reputation: 19248

Select COUNT in two table in one query with MYSQL

Is there a way i can select the COUNT with two table with one single query.

At the moment i have the following and it doesn't work correctly.

SELECT 
COUNT(t1.id) as t1_amount,
COUNT(t2.id) as t2_amount
FROM
table1 t1,
table2 t2

Upvotes: 0

Views: 5427

Answers (2)

jmkeyes
jmkeyes

Reputation: 3771

As they are two separate queries and you want them in the same result set, use a UNION:

(SELECT COUNT(*) AS `table1_total` FROM `table1`)
  UNION
(SELECT COUNT(*) AS `table2_total` FROM `table2`);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Here is one way:

select (select count(*) from table1) as t1_amount,
       (select count(*) from table2) as t2_amount

Here is another way:

select t1.t1_amount, t2.t2_amount
from (select count(*) as t1_amount from table1) t1 cross join
     (select count(*) as t2_amount from table2) t2

Your method does not work because the , in the from clause does a cross join. This does a cartesian product between the two tables.

Upvotes: 7

Related Questions