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 Count Function with Partition By Clause

SQL aggregate function Count can be used without Group By clause with new enhancements in T-SQL introduced with SQL Server 2005.

SQL Count with Partition By clause is one of the new powerful syntax that t-sql developers can easily use. For example, while selecting a list of rows you can also calculate count of rows sharing the same field values without using subselects or SQL CTE (Common Table Expressions) in your query. SQL developers will find the new t-sql aggregate function Count with Partition By clause syntax a little bit unusual, but if you are familiar with Row_Number() function or to the other Window functions like Tile, NTile, etc you will soon get used to new sql Count function in your select queries.

Before running the below sample for sql COUNT function query in SQL Server, note that I'm using SQL Server 2008 R2 AdventureWorks sample database. The below T-SQL script query SalesOrderHeader table in order to list order info with additional fields showing total number of orders for the related customer, total number of orders for the sales person and shipment address.

COUNT(*) OVER (PARTITION BY CustomerId) [Orders for Customer],
COUNT(*) OVER (PARTITION BY SalesPersonId) [Orders for Sales Person],
COUNT(*) OVER (PARTITION BY ShipToAddressId) [Orders for Shipment Address]
FROM Sales.SalesOrderHeader

If you check the return result set of the above t-sql query, you will notice that the SELECT list includes aggregated count numbers for customer, sales person and shipment address.

SQL Count function with Partition By clause

SQL programmers can find an other example at Count(*) Over Partition By Clause tutorial

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.