Sql: Combine Select count(*) from multiple tables

sql

join

subquery

performance

byNikita Barsukov·Mar 7, 2025

To aggregate counts across different tables in SQL, leverage the UNION ALL operator within subqueries. It is good practice to label each subquery result with an appropriate alias for clarity:

SELECT 'Table1' AS TableName, COUNT(*) AS Count FROM Table1
UNION ALL
SELECT 'Table2', COUNT(*) FROM Table2
UNION ALL
SELECT 'Table3', COUNT(*) FROM Table3;

This script will generate a combined list of counts from the specified tables in a single result set.

Pulling off a unified total count

For a grand total count across all tables, wrap the COUNT(*) statements within a single subquery, after which you calculate the sum:

SELECT SUM(Count) AS TotalCount FROM (
  SELECT COUNT(*) AS Count FROM Table1
  UNION ALL
  SELECT COUNT(*) FROM Table2
  UNION ALL
  SELECT COUNT(*) FROM Table3
) AS SubQuery;

This script gives out a single value encapsulating the overall count from the tables in question.

Integrating filters prior to counting

To count the rows in each table subject to certain filtering criteria, include WHERE clauses in the individual subqueries:

SELECT SUM(Count) AS TotalCountFiltered FROM (
  SELECT COUNT(*) AS Count FROM Table1 WHERE Condition1
  UNION ALL
  SELECT COUNT(*) FROM Table2 WHERE Condition2
  UNION ALL
  SELECT COUNT(*) FROM Table3 WHERE Condition3
) AS SubQuery;

This script sums up counts from each table where the specific conditions are met, outputting a total count that aligns with established filtering metrics.

Mastering the specifics

Summoning lightning for large databases

Working with large datasets, especially when summing up the entire results of UNION ALL subqueries, causes significant strain on performance. To circumvent this, consider storing intermediate counts using indexed views or temporary tables:

-- Creating Temp Table to store counts from Table1
SELECT COUNT(*) as Count INTO #Temp FROM Table1;

-- A wizard's incantation for efficiency
-- (Also known as Indexing)
CREATE CLUSTERED INDEX idx_temp ON #Temp (Count);

-- Doing the same for Table2 
INSERT INTO #Temp SELECT COUNT(*) FROM Table2;

-- Now we SUM them up. Abracadabra!
SELECT SUM(Count) FROM #Temp;

Schemas vs The Undead

When faced with disparate schema structures (a kinda zombie apocalypse for SQL devs), arm yourself with AS aliases to ensure your COUNT(*) columns align for the UNION ALL operation. Remember, consistent column naming is the silver bullet:

-- Crossbows and holy water ready!
SELECT COUNT(*) AS UniversalCount FROM TableWithDifferentSchema WHERE Condition1
UNION ALL
SELECT COUNT(DifferentColumn) FROM AnotherTable WHERE Condition2;

Wrangling complex conditions with CTEs

Complex filtering conditions can make your SQL queries look like a spaghetti monster. To restore order, use Common Table Expressions (CTEs) to segregate filtering logic before performing the counts. This enhances readability and may boost performance:

-- CTEs are like Ghostbusters for your queries!
WITH CTE_Table1 AS (
  SELECT * FROM Table1 WHERE ComplexCondition1
),
CTE_Table2 AS (
  SELECT * FROM Table2 WHERE ComplexCondition2
)
-- Who you gonna call?
SELECT SUM(Count) AS TotalCount FROM (
  SELECT COUNT(*) AS Count FROM CTE_Table1
  UNION ALL
  SELECT COUNT(*) FROM CTE_Table2
) AS SubQuery;