SQL OVER and PARTITION BY T-SQL

Jen McCown blog explains so nicely Over and Partition By usage of SQL functions.

OVER

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, this query:

SELECT SUM(Cost) OVER () AS Cost
, OrderNum
FROM Orders

Will return something like this:

Cost    OrderNum
10.00     345
10.00     346
10.00     347
10.00     348

Quick translation:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • () – …that encompasses the entire result set.

OVER(PARTITION BY)

OVER, as used in our previous example, exposes the entire resultset to the aggregation…”Cost” was the sum of all [Cost]  in the resultset.  We can break up that resultset into partitions with the use of PARTITION BY:

SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
, OrderNum
, CustomerNo

FROM Orders

My partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”….I’ll get the sum of cost for Customer 1, and then the sum for Customer 2:

Cost  OrderNum   CustomerNo
8.00   345          1
8.00   346          1
8.00   347          1
2.00   348          2

The translation here is:

  • SUM(cost) – get me the sum of the COST column
  • OVER – for the set of rows….
  • (PARTITION BY CustomerNo) – …that have the same CustomerNo.

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

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  

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s