I have been working on SQL Server Consulting assignment, a SQL Server 2014 migration for a new client over the past few weeks. When I’m undertaking such tasks I’m often asked additional questions that make my day a little interesting.
I got a question last week How to Count the Total Number of Rows Across Multiple Tables.
I got a question last week How to Count the Total Number of Rows Across Multiple Tables.
Lets look at a simple example
T1 has 13 rows
T2 has 12 rows
T3 has 12 rows
The scripts to create these tables and temporary tables in temp db is
CREATE TABLE #t1 ([c1] [nchar](10) NULL) ON [PRIMARY]
CREATE TABLE #t2 ([c1] [nchar](10) NULL) ON [PRIMARY]
CREATE TABLE #t3 ([c1] [nchar](10) NULL) ON [PRIMARY]
/*Insert Some data to the tables*/
DECLARE @i INT
SET @i = 1
WHILE @i & lt;= 13
BEGIN
INSERT INTO #t1 (c1)
VALUES ('x')
SET @i = @i + 1
END
DECLARE @x INT
SET @x = 1
WHILE @x & lt;= 12
BEGIN
INSERT INTO #t2 (c1)
VALUES ('x')
INSERT INTO #t3 (c1)
VALUES ('x')
SET @x = @x + 1
END
The questions seems like a simple one. We can use the aggregate functions in SQL to help do the maths in terms of counting the rows However the solution needs to two steps.
Like all things in SQL there is more than one way to achieve the same outcome this is the solution I came up and I’ve broken it down into separate parts.
Using the COUNT Aggregate function we can quickly count the rows in one table. The first query counts all the rows in table t1
Using the COUNT Aggregate function we can quickly count the rows in one table. The first query counts all the rows in table t1
SELECT COUNT(*)
FROM #t1
So we have a starting point. Next we need count all the rows in the second table
SELECT COUNT(*)
FROM #t2
Will give you a count of all the rows in table2.
We need to repeat this for all the tables that we want to include in the final total row number.
We need to repeat this for all the tables that we want to include in the final total row number.
When we have counted rows in all the necessary tables individually we then need to combine the individual results into one result set.
We can do this with a UNION ALL. This will combine the counts for each table into a single result set.
The UNION ALL is important. A UNION does an implicit distinct so any tables with the same number of rows will result in the duplicates being removed. Compare the two results below
SELECT COUNT(*)
FROM #t1
UNION
SELECT COUNT(*)
FROM #t2
UNION
SELECT COUNT(*)
FROM #t3
Tables t2 and t3 each have 12 rows each. If we build our table row count table with a UNION it will do implicit distinct and remove duplicates. therefore we have two rows in the result set that have the value 12 so only one is included with the UNION. Therefore our next calculation of adding all the counts together will be wrong
UNION ALL does not do the implicit distinct duplicates remain in the final result. Which is what we want in this case.
SELECT COUNT(*)
FROM #t1
UNION ALL
SELECT COUNT(*)
FROM #t2
UNION ALL
SELECT COUNT(*)
FROM #t3
So we have a result that is giving us the count of the rows in each table. Now we need to add the row totals together. We can do that by putting our UNION ALL query into a derived table or common table expression (CTE). An inline view if you prefer. The CTE or derived table will allow us to include the result set of our UNION ALL statement in the FROM clause and the SELECT from it to sum the total rows.
In this example I will use a CTE
;with cterc as
(SELECT COUNT(*) as rn
FROM #t1
UNION ALL
SELECT COUNT(*)
FROM #t2
UNION ALL
SELECT COUNT(*)
FROM #t3)
SELECT SUM(rn) as totalrowNo
from cterc
I use in SUM in the outer query to add the number of rows of each table in the final result set. Giving us 37 rows across the 3 tables t1, t2 and t3
If you are looking to do this yourself. You need to do the following:
- Use SELECT COUNT (*) on each table to have its rowed total
- Use UNION ALL to build a result of the row count of each table
- Wrap that result set in CTE or derived table
- Select from the CTE or derived table SUMing the row count column
You can learn all about querying SQL Server on Learning Tree’s 534 Writing SQL Queries for Microsoft SQL Server course. If you would like attend the event running in London starting Feburary 7th either in class or online you can book a seat at a special price of £750 plus VAT. Email training@gethynellis.com for more details or to book your seat.
