The Transact-SQL OVER Clause: Not Just For Ranking Functions

If you’ve been following this blog for a bit, you may have seen the post I made back in March on Ranking Functions. In it I introduced ranking functions: RANK(), DENSE_RANK(), and ROW_NUMBER(). I also introduced the OVER clause, and how it is used to order and partition the ranking logic.

Until now, I didn’t realize that the OVER clause could be applied to aggregate functions as well. Earlier this week, I dug up this little nugget from BOL.

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'

FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);

Holy cow, Batman! How did I miss that? The partitioned aggregates very efficiently perform subtotals based upon the OVER clause that you define. Prior to the OVER clause, we would have needed to create a derived table which GROUPed the query by our partition columns, then joined said table back to our parent query. This method is much cleaner, and much more efficient, as you aren’t running through your results twice (one for the aggregate, and again to fetch back the “rows” themselves).

This opens up a lot of possibilities, so it’s a good tool to have in your belt, so to speak.

Tune in later this week, as we return to our regularly scheduled BI series.