O'Reilly logo

Microsoft® SQL Server 2012 Unleashed by Alex T. Silverstein, Chris Gallelli, Paul T. Bertucci, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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" ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required