How to add Count output from different tables in SQL Server?

Asked

Viewed 2,090 times

4

How to add up the result of Count of different tables in SQL Server?

Example:

Table WORKS has 755614 Records

SELECT count(*) from OBRAS;

Table TITLES has 85106 Records

SELECT count(*) from TITULOS;

Table AUTHORS has 19029 Records

SELECT count(*) from AUTORES;
sql sql-server count

  • Have any answers solved what was in doubt? Do you need something else to be improved? Do you think it is possible to accept it now?

3 answers

4

There is no reason 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 put in the Github for future reference.

It would be interesting another solution if the list of tables were very large, but essentially it would involve code that generalizes this or use of auxiliary tables.

3

If you’re not so concerned about 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
  • 1

    I would suggest replacing UNION with UNION ALL. If two tables have the same number of records will count only half.

  • good, changing the answer ;)

3

Simple question but at the same time curious. 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 looked at the implementation plans for the 3 solutions proposed and realized that 2 solutions generate similar (perhaps identical) implementation plans while the third generates a different implementation plan.

  • 1

    Exactly what you described, arose this need to check the amount of records, something really simple but interesting.

Browser other questions tagged sql sql-server count

You are not signed in. Login or sign up in order to post.