56

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

Kiquenet
  • 14,494
  • 35
  • 148
  • 243
Jack
  • 9,843
  • 23
  • 78
  • 111

8 Answers8

119
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.

Chris J
  • 2,160
  • 4
  • 16
  • 23
  • 1
    Do we know how efficient this is on large tables of data? – JDandChips Feb 01 '13 at 14:49
  • The select count(*) should be based off an index on ID if available, and if one is not looking at the data it should be added. After that the addition is relatively simple. – Chris J Mar 12 '13 at 13:22
21

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'
);
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    Actually I see it's similar to the answer given by @Gren but this example is more concrete w.r.t. the OP's question. – Bill Karwin Aug 14 '09 at 19:38
  • 1
    This works on SQL Server Compact Edition whereas sub-queries in the `SELECT` statement will fail. – Ryan Kirkman Mar 25 '13 at 05:17
  • But wouldn't union all be slower than just adding the results? As specified in Chris' answer? – shabby May 26 '13 at 20:59
  • Hard to tell without testing them head to head on the same dataset and same hardware. – Bill Karwin May 27 '13 at 00:08
  • @BoratSagdiyev, my solution uses UNION in a derived table, and the accepted answer uses three scalar subqueries. One may perform better than the other in a given RDBMS, and that depends on the query optimizer implementation in your RDBMS product. – Bill Karwin Mar 30 '14 at 14:40
  • Wouldn't this yield wrong result if the count(*) of any two (or more) subqueries were the same value? because the UNION would make it distinct and "duplicate" counts would not get added. – Saharsh Jul 06 '18 at 22:51
  • @BayakfromSiwa, When we use `UNION ALL` it does not eliminate duplicates. – Bill Karwin Jul 06 '18 at 22:52
18
select 
  (select count(*) from foo) as foo
, (select count(*) from bar) as bar
, ...
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
10

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,
Mitchel Sellers
  • 62,228
  • 14
  • 110
  • 173
3

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
Jason
  • 985
  • 1
  • 6
  • 12
1
select sum(counts) from (
select count(1) as counts from foo 
union all
select count(1) as counts from bar)
Gren
  • 573
  • 2
  • 9
0

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.

Kris
  • 40,604
  • 9
  • 72
  • 101
0

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;
toquart
  • 394
  • 2
  • 8