How to add the count result of different tables in SQL Server?
Example:
Table WORKS has 755614 Records
SELECT count(*) from OBRAS;
TITLES table has 85106 Records
SELECT count(*) from TITULOS;
AUTHORS table has 19029 Records
SELECT count(*) from AUTORES;
It does not have to complicate, if you want to add several things is a simple arithmetic operation.
SELECT (SELECT count(*) from OBRAS) + (SELECT count(*) from TITULOS) + (SELECT count(*) from AUTORES);
I placed GitHub for future reference.
It would be interesting if the list of tables were too large, but essentially would involve code that would generalize this or use auxiliary tables.
If you are not so concerned with performance, you can use the query below:
select sum(total) from
(SELECT count(*) as total from OBRAS
union all
SELECT count(*) as total from TITULOS
union all
SELECT count(*) as total from AUTORES) as totalConsultas
Simple but at the same time curious question. There are some solutions. For example, in addition to the solutions proposed by rLinhares and Maniero, here is another:
-- código #1
with
C1 as (SELECT count(*) as Q from OBRAS),
C2 as (SELECT count(*) as Q from TITULOS),
C3 as (SELECT count(*) as Q from AUTORES)
SELECT C1.Q + C2.Q + C3.Q
from C1 cross join C2 cross join C3;
I analyzed the execution plans of the 3 proposed solutions and realized that 2 solutions generate similar (maybe identical) execution plans while the third one generates different execution plan.