Select COUNT in two table in one query with MYSQL

Question

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

Gordon Linoff · Accepted Answer · 2013-04-18 01:12:01Z

7

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.

answered Apr 18, 2013 at 1:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bill Over a year ago

Thanks Gordon which method is more efficient?

Gordon Linoff Over a year ago

@bluebill1049 . . . They should be the same in terms of efficiency. Personally, I prefer the second approach because I tend to avoid select in select statements.

Bill Over a year ago

Thanks Gordon, i will follow your advice

jmkeyes · Accepted Answer · 2013-04-18 01:20:07Z

2

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`);

answered Apr 18, 2013 at 1:20

jmkeyes

3,77119 silver badges20 bronze badges

1 Comment

Bill Over a year ago

Thanks Joshua for the answer

Collectives™ on Stack Overflow

Select COUNT in two table in one query with MYSQL

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related