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 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.

SELECT
  Name,
  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:

WITH CTE AS (
  SELECT FirstName
  FROM Person.Person
  WHERE LastName LIKE N'A%'
), CountAll AS (
  SELECT COUNT(*) RowsCount FROM CTE
)
SELECT * FROM CTE, CountAll

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.






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









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