SQL: Combine Select count(*) from multiple tables
SqlSql ServerTsqlSql Problem Overview
How do you combine multiple select count(*) from different table into one return?
I have a similar sitiuation as this post
but I want one return.
I tried Union all but it spit back 3 separate rows of count. How do you combine them into one?
select count(*) from foo1 where ID = '00123244552000258'
union all
select count(*) from foo2 where ID = '00123244552000258'
union all
select count(*) from foo3 where ID = '00123244552000258'
edit: I'm on MS SQL 2005
Sql Solutions
Solution 1 - Sql
SELECT
(select count(*) from foo1 where ID = '00123244552000258')
+
(select count(*) from foo2 where ID = '00123244552000258')
+
(select count(*) from foo3 where ID = '00123244552000258')
This is an easy way.
Solution 2 - Sql
I'm surprised no one has suggested this variation:
SELECT SUM(c)
FROM (
SELECT COUNT(*) AS c FROM foo1 WHERE ID = '00123244552000258'
UNION ALL
SELECT COUNT(*) FROM foo2 WHERE ID = '00123244552000258'
UNION ALL
SELECT COUNT(*) FROM foo3 WHERE ID = '00123244552000258'
);
Solution 3 - Sql
select
(select count(*) from foo) as foo
, (select count(*) from bar) as bar
, ...
Solution 4 - Sql
Basically you do the counts as sub-queries within a standard select.
An example would be the following, this returns 1 row, two columns
SELECT
(SELECT COUNT(*) FROM MyTable WHERE MyCol = 'MyValue') AS MyTableCount,
(SELECT COUNT(*) FROM YourTable WHERE MyCol = 'MyValue') AS YourTableCount,
Solution 5 - Sql
You can combine your counts like you were doing before, but then you could sum them all up a number of ways, one of which is shown below:
SELECT SUM(A)
FROM
(
SELECT 1 AS A
UNION ALL
SELECT 1 AS A
UNION ALL
SELECT 1 AS A
UNION ALL
SELECT 1 AS A
) AS B
Solution 6 - Sql
you could name all fields and add an outer select on those fields:
SELECT A, B, C FROM ( your initial query here ) TableAlias
That should do the trick.
Solution 7 - Sql
select sum(counts) from (
select count(1) as counts from foo
union all
select count(1) as counts from bar)
Solution 8 - Sql
For oracle:
select(
select count(*) from foo1 where ID = '00123244552000258'
+
select count(*) from foo2 where ID = '00123244552000258'
+
select count(*) from foo3 where ID = '00123244552000258'
) total from dual;