Menu
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
615
SQL SUM() and COUNT() with inner join The data from a subquery can be stored in a temporary table or alias. The data of these temporary tables can be used to manipulate data of another table. These two tables can be joined by themselves and to return a result.
Conclusion. In this short tutorial, you have seen how the COUNT/GROUP BY/JOIN combination can be used in SQL to aggregate entries across multiple tables. While a GROUP BY query can accomplish this simply enough when working with just one table, the situation can become more complex when working across multiple tables.
Since it doesn't matter which value you put in the parentheses, it follows that COUNT(*) and COUNT(1) are precisely the same. They are precisely the same because the value in the COUNT() parentheses serves only to tell the query what it will count.
To achieve this for multiple tables, use the UNION ALL. select sum(variableName. aliasName) from ( select count(*) as yourAliasName from yourTableName1 UNION ALL select count(*) as yourAliasName from yourTableName2 ) yourVariableName; Let us implement the above syntax.
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.
52
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' ); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
