SELECT - OVER

Determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

Syntax

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )

Arguments

PARTITION BY

Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

value_expression

Specifies the column by which the rowset is partitioned. value_expression can only refer to columns made available by the FROM clause. value_expression cannot refer to expressions or aliases in the select list. value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.

<ORDER BY clause>

Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window functioncalculation is performed.

order_by_expression

Specifies a column or expression on which to sort. order_by_expression can only refer to columns made available by the FROM clause. An integer cannot be specified to represent a column name or alias.

ASC | DESC

Specifies that the values in the specified column should be sorted in ascending or descending order. ASC is the default sort order. Null values are treated as the lowest possible values.

Examples

A. Using the OVER clause with the ROW_NUMBER function

The following example shows using the OVER clause with ROW_NUMBER function to display a row number for each row within a partition. The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. The ORDER BY clause in the SELECT statement determines the order in which the entire query result set is returned.

USE AdventureWorks2012;

SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
       p.LastName, s.SalesYTD, a.PostalCode
  FROM Sales.SalesPerson AS s
 INNER
  JOIN Person.Person AS p
    ON s.BusinessEntityID = p.BusinessEntityID
 INNER
  JOIN Person.Address AS a
    ON a.AddressID = p.BusinessEntityID
 WHERE TerritoryID IS NOT NULL
   AND SalesYTD <> 0
 ORDER BY PostalCode;

Here is the result set.

Row Number      LastName                SalesYTD              PostalCode
--------------- ----------------------- --------------------- ----------
1               Mitchell                4251368.5497          98027
2               Blythe                  3763178.1787          98027
3               Carson                  3189418.3662          98027
4               Reiter                  2315185.611           98027
5               Vargas                  1453719.4653          98027
6               Ansman-Wolfe            1352577.1325          98027
1               Pak                     4116871.2277          98055
2               Varkey Chudukatil       3121616.3202          98055
3               Saraiva                 2604540.7172          98055
4               Ito                     2458535.6169          98055
5               Valdez                  1827066.7118          98055
6               Mensa-Annan             1576562.1966          98055
7               Campbell                1573012.9383          98055
8               Tsoflias                1421810.9242          98055

B. Using the OVER clause with aggregate functions

The following example uses the OVER clause with aggregate functions over all rows returned by the query. In this example, using the OVER clause is more efficient than using subqueries to derive the aggregate values.

USE AdventureWorks2012;

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);

Here is the result set:

SalesOrderID ProductID   OrderQty Total       Avg         Count       Min    Max
------------ ----------- -------- ----------- ----------- ----------- ------ ------
43659        776         1        26          2           12          1      6
43659        777         3        26          2           12          1      6
43659        778         1        26          2           12          1      6
43659        771         1        26          2           12          1      6
43659        772         1        26          2           12          1      6
43659        773         2        26          2           12          1      6
43659        774         1        26          2           12          1      6
43659        714         3        26          2           12          1      6
43659        716         1        26          2           12          1      6
43659        709         6        26          2           12          1      6
43659        712         2        26          2           12          1      6
43659        711         4        26          2           12          1      6
43664        772         1        14          1           8           1      4
43664        775         4        14          1           8           1      4
43664        714         1        14          1           8           1      4
43664        716         1        14          1           8           1      4
43664        777         2        14          1           8           1      4
43664        771         3        14          1           8           1      4
43664        773         1        14          1           8           1      4
43664        778         1        14          1           8           1      4

The following example shows using the OVER clause with an aggregate function in a calculated value.

USE AdventureWorks2012;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
    ,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID)
        *100 AS DECIMAL(5,2))AS "Percent by ProductID"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO

Here is the result set. Notice that the aggregates are calculated by SalesOrderID and the Percent by ProductID is calculated for each line of each SalesOrderID.

SalesOrderID ProductID   OrderQty Total       Percent by ProductID
------------ ----------- -------- ----------- --------------------
43659        776         1        26          3.85
43659        777         3        26          11.54
43659        778         1        26          3.85
43659        771         1        26          3.85
43659        772         1        26          3.85
43659        773         2        26          7.69
43659        774         1        26          3.85
43659        714         3        26          11.54
43659        716         1        26          3.85
43659        709         6        26          23.08
43659        712         2        26          7.69
43659        711         4        26          15.38
43664        772         1        14          7.14
43664        775         4        14          28.57
43664        714         1        14          7.14
43664        716         1        14          7.14
43664        777         2        14          14.29
43664        771         3        14          21.4
43664        773         1        14          7.14
43664        778         1        14          7.14

(20 row(s) affected)

C. Producing a moving average and cumulative total

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 data is partitioned by TerritoryID and logically ordered by SalesYTD. This means that the AVG function is computed for each territory based on the sales year. Notice that for TerritoryID, there are two rows for sales year 2005 representing the two sales people with sales that year. The average sales for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation.

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, TerritoryID
     , DATEPART(yy,ModifiedDate) AS SalesYear
     , CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
     , CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
                                               ORDER BY DATEPART(yy,ModifiedDate)
                                              ),1) AS MovingAvg
     , CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                               ORDER BY DATEPART(yy,ModifiedDate)
                                               ),1) AS CumulativeTotal
  FROM Sales.SalesPerson
 WHERE TerritoryID IS NULL OR TerritoryID < 5
 ORDER BY TerritoryID,SalesYear;

Here is the result set:

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           559,697.56           559,697.56
287              NULL        2006        519,905.93           539,801.75           1,079,603.50
285              NULL        2007        172,524.45           417,375.98           1,252,127.95
283              1           2005        1,573,012.94         1,462,795.04         2,925,590.07
280              1           2005        1,352,577.13         1,462,795.04         2,925,590.07
284              1           2006        1,576,562.20         1,500,717.42         4,502,152.27
275              2           2005        3,763,178.18         3,763,178.18         3,763,178.18
277              3           2005        3,189,418.37         3,189,418.37         3,189,418.37
276              4           2005        4,251,368.55         3,354,952.08         6,709,904.17
281              4           2005        2,458,535.62         3,354,952.08         6,709,904.17

(10 row(s) affected)

In this example, the OVER clause does not include PARTITION BY. This means that the function will be applied to all rows returned by the query. The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function is applied. The query returns a moving average of sales by year for all sales territories specified in the WHERE clause. The ORDER BY clause specified in the SELECT statement determines the order in which the rows of the query are displayed.

SELECT BusinessEntityID, TerritoryID
     , DATEPART(yy,ModifiedDate) AS SalesYear
     , CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
     , CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                               ),1) AS MovingAvg
     , CONVERT(varchar(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
                                            ),1) AS CumulativeTotal
 FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;

Here is the result set:

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           2,449,684.05         17,147,788.35
275              2           2005        3,763,178.18         2,449,684.05         17,147,788.35
276              4           2005        4,251,368.55         2,449,684.05         17,147,788.35
277              3           2005        3,189,418.37         2,449,684.05         17,147,788.35
280              1           2005        1,352,577.13         2,449,684.05         17,147,788.35
281              4           2005        2,458,535.62         2,449,684.05         17,147,788.35
283              1           2005        1,573,012.94         2,449,684.05         17,147,788.35
284              1           2006        1,576,562.20         2,138,250.72         19,244,256.47
287              NULL        2006        519,905.93           2,138,250.72         19,244,256.47
285              NULL        2007        172,524.45           1,941,678.09         19,416,780.93
(10 row(s) affected)

See Also

  • Functions
  • Excellent blog post about window functions and OVER, on sqlmag.com, by Itzik Ben-Gan