SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.

download SQL Server 2016
download SQL Server 2014

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.

  TotalRows = COUNT(*) OVER (PARTITION BY 1)
FROM sys.tables

sql select count over partition by

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:

  SELECT FirstName
  FROM Person.Person
  WHERE LastName LIKE N'A%'
), CountAll AS (

sql row count with cte

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.

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2015 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems