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.

score 4 · Accepted Answer · answered Dec 16 '10 at 06:38

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 '10 at 06:38

Pentium10

Lee · Answer 2 · 2010-12-16T06:42:59.953

1

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

edited Dec 16 '10 at 06:42

answered Dec 16 '10 at 06:35

Lee

If both subqueries return 30, it won't return 60, instead will return 30. – Pentium10 Dec 16 '10 at 06:39
indeed. thanks for that. I'll make the correction... though you've already posted the answer yourself I see. – Lee Dec 16 '10 at 06:42
@Pentium, As mentioned above comments, what is the fix for If both subqueries return 30, it won't return 60, instead will return 30 – gmhk Dec 16 '10 at 07:43

2 Answers2