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

MinionWare logo

Check out Minion Enterprise, our new enterprise management solution for centralized SQL Server management and alerting! 
Work like a DBA.

27 thoughts on “Tip: OVER and PARTITION BY”

  1. Oscar Zamora

    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. Pingback: Tweets that mention SQL Awesomesauce » Blog Archive » Tip: OVER and PARTITION BY -- Topsy.com

  3. Pingback: SQL Awesomesauce » Blog Archive » News This Week (Oct 11-15)

  5. Pingback: SQL Awesomesauce » Blog Archive » T-SQL Tuesday #016: Aggregates and Windowing Functions and Ranking! Yum!

  6. Pingback: SQL Awesomesauce » Blog Archive » Learn T-SQL in 5 Minutes

  8. John Rees

    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. Jen McCown Post author

      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!

  9. Patricia

    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.

  10. ysl t shirts

    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. …

  11. Guppii

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

  12. Pingback: SQL: Over and Over(Partition By) | Just BeCos'

  13. Darya

    Thank you! Excellent explanations and simple examples – easy to understand the logic.

  14. Pingback: TSQL - OVER and PARTITION BY | mKnCreations

  19. Connor Maynes

    Thank you for this explanation. I was starting to give up on figuring out how partitions worked, until I read this. I was going to turn to cursors, but I know their use is kindof frowned upon, since there is a big drop in efficiency.

    Now I love partitions. Thanks again.

  20. because

    Pretty part օf content. I јust stumbled upon your site and in accession capitaⅼ to ｃlaim that I acquire іn faсt ⅼoved accoᥙnt your blog posts.
    Any way I will be subscribing to your aսgment and even I fulfіllment you get right of entry t᧐ persiѕtently
    fast.

  21. Yvonne

    I’m editing a report with OVER and PARTITION BY and the Microsoft docs were like gibberish! So glad I stumbled across this post, It explained exactly what they did in nice simple, easy to understand terms and examples. ^-^

  23. Pingback: Grouping Data using the OVER and PARTITION BY Functions | {coding}Sight

  24. VK

    Try to use partition by on existing table but after using it its changing sequence of records with filed which I have given in partition please find my query below

    SELECT Field1,Field2,Field3,row_number() OVER(Partition by LASTUP )
    as SEQNO FROM FILE01

Comments are closed.