SQL Select Count of ResultSet Rows using COUNT(*) OVER PARTITION BY
T-SQL developers frequently require sql select count of the resultset beside the rows data itself on a separate column.
The SQL Count Over Partition By clause helps a lot to the database developers in the solution of such a tsql problem.
Here is a small SELECT statement trick to return the total rows count within the returned result set.
The COUNT(*) OVER (PARTITION BY 1) makes the trick.
SELECT
Name,
TotalRows = COUNT(*) OVER (PARTITION BY 1)
FROM sys.tables
You can see the number of rows within the returned sql result set is displayed in an additional sql column TotalRows.
Actually, the SQL Count() Over Partition By syntax is a similar tsql usage of the ROW_NUMBER Over Partition By syntax. But this new syntax in SQL Server aggregate function Count, helps developers avoid the "Group By" clause which was obligatory to use with Count() function just as other aggregate functions.
An other method ro return the sql resultset with numbers of rows in it is using T-SQL CTE expression.
SQL developers can use the T-SQL multiple CTE syntax as follows:
WITH CTE AS (
SELECT FirstName
FROM Person.Person
WHERE LastName LIKE N'A%'
), CountAll AS (
SELECT COUNT(*) RowsCount FROM CTE
)
SELECT * FROM CTE, CountAll
As you can see from the sql result list, the return set includes a sql column named RowsCount which has the total rows count of the result set.