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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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
Code

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.