Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tip: OVER and PARTITION BY

Here’s a quick summary of OVER and PARTITION BY (new in SQL 2005), for the uninitiated or forgetful…

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.

 

Further Reading: BOL: OVER Clause

Enjoy, and happy days!

-Jen

http://www.MidnightDBA.com/Jen

Comments

Posted by Anonymous on 5 October 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Tip: OVER and PARTITION BY - SQL Awesomesauce         [sqlservercentral.com]        on Topsy.com

Leave a Comment

Please register or log in to leave a comment.