Home » Beginner »sql server »sqlserverpedia-syndication »SSC »Tips »TSQL » Currently Reading:

Tip: OVER and PARTITION BY

October 5, 2010 Beginner, sql server, sqlserverpedia-syndication, SSC, Tips, TSQL 14 Comments

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

 

Currently there are "14 comments" on this Article:

  1. Oscar Zamora says:

    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. [...] This post was mentioned on Twitter by Paul Montgomery and Dukagjin Maloku, Jen & Sean McCown. Jen & Sean McCown said: Blog: Tip: OVER and PARTITION BY: Here’s a quick summary of OVER and PARTITION BY (new in SQL 2005), for the unini… http://bit.ly/dsxpdR [...]

  3. [...] My blog this week: Tip: OVER and PARTITION BY [...]

  4. Piotr Rodak says:

    Thanks Jen, that’s cool stuff. I think I know a few scripts to put it into.

  5. [...] Let’s get this out of the way right now: I am SO going to take advantage of this T-SQL Tuesday on aggregates to talk about OVER() and PARTITION BY. Aggregates and OVER go together like french fries and catsup…sure, you can have them apart, but together they’re just magical. Note: If you’re not at all familiar with OVER (PARTITION BY) at all, take a quick look at my brief intro, Tip: Over and Partition By. [...]

  6. Saraswathi says:

    That’s useful info. Thank you.

  7. John Rees says:

    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.

    • Jen McCown says:

      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!

  8. Patricia says:

    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.

  9. ysl t shirts says:

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

  10. Guppii says:

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

  11. Darya says:

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

Comment on this Article:







Release Date: Minion by MidnightDBA

Just 3 days 2 hours 49 minutes 26 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/