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 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 Count function with Partition By clause

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





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