Search This Blog

Sunday, 22 January 2017

How to Count the Total Number of Rows Across Multiple Tables

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.

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

SELECTCOUNT(*)FROM #t1

So we have a starting point. Next we need count all the rows in the second table

SELECTCOUNT(*)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.

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

SELECTCOUNT(*)

FROM #t1

UNION

SELECTCOUNT(*)

FROM #t2

UNION

SELECTCOUNT(*)

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.

SELECTCOUNT(*)

FROM #t1

UNIONALL

SELECTCOUNT(*)

FROM #t2

UNIONALL

SELECTCOUNT(*)

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

(SELECTCOUNT(*)as rn

FROM #t1

UNIONALL

SELECTCOUNT(*)

FROM #t2

UNIONALL

SELECTCOUNT(*)

FROM #t3)

SELECTSUM(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:

Yep that works for me - it has a slightly different query plan to my solution. Although with this small amount of data it doing the same number of reads. It would be interesting to see how both perform on a larger data set