Microsoft® SQL Server 2012 Unleashed

Calculating Aggregates with the OVER Clause

The OVER clause can be used with standard aggregate functions over all the rows returned by a query while generating aggregate values in the result along with the detail rows. The PARTITION BY clause is used to specify the subgroups within the result set base for which the aggregates should be calculated, as shown in the following example:

Click here to view code image

SELECT SalesOrderID, ProductID, OrderQty    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max" ...