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:
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" ...
Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
