Microsoft® SQL Server 2012 Unleashed

SQL Server 2012 Enhancements to Windowing Functions

In this section, we explore the SQL Server 2012 features and enhancements to the windowing functions.

Ordering for Aggregates

In SQL Server 2012, you can now include the ORDER BY clause in the OVER clause with standard aggregate functions. This capability allows you to generate moving averages and cumulative totals. The following example uses the AVG and SUM functions with the OVER clause to provide a moving average and cumulative total of yearly sales for each territory in the Sales.SalesPerson table. The PARTITION BY clause defines the partition on TerritoryID and logically ordered by the year stored in the ModifiedDate column. This computes the average value of SalesYTD for each territory ...