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


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:

, 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, 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:

, 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!


MinionWare logo

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

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

    2 city, SUM(salary) AS city_salary,
    4 cumulative_salary
    5 FROM employee
    6 GROUP BY city
    7 ORDER BY city;


    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!

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

  7. Pretty part օf content. I јust stumbled upon your site and in accession capitaⅼ to claim 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

  8. 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. ^-^

Leave a Reply

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