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

June 2012 edit: We highly, highly recommend Itzik Ben-Gan’s brand new book Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions for an outstanding and thorough explanation of windowing functions (including OVER / PARTITION BY).

Enjoy, and happy days!

-Jen
http://www.MidnightDBA.com/Jen

 

16 thoughts on “Tip: OVER and PARTITION BY”

  1. I hated SQL 2000 when I moved over from Oracle 8.1.6 as it did not have windowed analytical functions. 2005 fixed it but still missing some of the advanced windowed stuff provided by Oracle.

    SQL> SELECT
    2 city, SUM(salary) AS city_salary,
    3 SUM(SUM(salary)) OVER (ORDER BY city ROWS UNBOUNDED PRECEDING) AS
    4 cumulative_salary
    5 FROM employee
    6 GROUP BY city
    7 ORDER BY city;

    http://goo.gl/NeKL

    Still a great tip.

  2. Sometimes the short tips are the best! This little snippet completely opened my eyes to this feature. I’d only ever really seen OVER used with row numbering for pagination, but this aggregation over partitions is awesome.

    I know it’s old news really, but it’s only just hit me how cool this is going to be for my work. Lots of opportunities for eliminating temp tables I think.

    1. It’s old news, but I say time and time again: you can’t catch every new feature in SQL Server. And yes, sometimes it takes 7-8 years to stumble on something like this. That’s a major reason why I give a “Forgotten T-SQL” talk.

      Glad you liked this!

  3. As a QA tester with very limited SQL skills, this explanation was invaluable. I now understand and can use this in my own queries when I independently assess whether the test results are correct.

  4. Great items, I realized your website online on google and yahoo and scan two of the additional posts. I simply added yourself to my Google and yahoo News Subscriber. Keep the great math function Watch for reading extraordinary from you later in life. …

  5. Nice simple examples with great explanations!
    I hate when people provide a huge query with 20 columns to demonstrate a simple concept!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>