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.
SELECT
SalesOrderNumber,
CustomerId,
COUNT(*) OVER (PARTITION BY CustomerId) [Orders for Customer],
SalesPersonId,
COUNT(*) OVER (PARTITION BY SalesPersonId) [Orders for Sales Person],
ShipToAddressId,
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 programmers can find an other example at Count(*) Over Partition By Clause tutorial