Database Query to get sum of count from different tables in a single query

Question

I am writing a query in Mysql database, in which Query 1 returns count() say result is 10 and Query 2 returns Count() say result is 30

But I want to get the result as 40, which is sum of both

what are my options to get a single query giving me the result.

Pentium10 · Accepted Answer · 2010-12-16 06:38:25Z

4

You should use UNION ALL to union also the same valued counts like 30+30.

select SUM(n) as total
from (
  (select count(*) as n from table1)
  UNION ALL
  (select count(*) as n from table2)
) t;

answered Dec 16, 2010 at 6:38

Pentium10

209k124 gold badges435 silver badges513 bronze badges

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

Comments

Lee · Accepted Answer · 2010-12-16 06:42:59Z

1

select sum(num) as total
from (
  (select count(*) as num from table1)
  UNION ALL
  (select count(*) as num from table2)
) a;

answered Dec 16, 2010 at 6:35

Lee

13.5k1 gold badge34 silver badges46 bronze badges

If both subqueries return 30, it won't return 60, instead will return 30.

indeed. thanks for that. I'll make the correction... though you've already posted the answer yourself I see.

@Pentium, As mentioned above comments, what is the fix for If both subqueries return 30, it won't return 60, instead will return 30