{"id":37620,"date":"2014-11-04T11:25:08","date_gmt":"2014-11-04T16:25:08","guid":{"rendered":"https:\/\/mkncreations.com\/site\/?p=37620"},"modified":"2014-11-04T11:25:43","modified_gmt":"2014-11-04T16:25:43","slug":"tsql-over-and-partition-by","status":"publish","type":"post","link":"https:\/\/mkncreations.com\/site\/2014\/11\/tsql-over-and-partition-by\/","title":{"rendered":"TSQL &#8211; OVER and PARTITION BY"},"content":{"rendered":"<p><em>Source<\/em>: <a href=\"http:\/\/www.midnightdba.com\/Jen\/2010\/10\/tip-over-and-partition-by\/\" title=\"http:\/\/www.midnightdba.com\/Jen\/2010\/10\/tip-over-and-partition-by\/\" target=\"_blank\">http:\/\/www.midnightdba.com\/Jen\/2010\/10\/tip-over-and-partition-by\/<\/a><\/p>\n<p>Here\u2019s a quick summary of OVER and PARTITION BY (new in SQL 2005), for the uninitiated or forgetful\u2026<\/p>\n<p><strong>OVER<\/strong><\/p>\n<p>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:<\/p>\n<blockquote><p>\nSELECT SUM(Cost) OVER () AS Cost<br \/>\n, OrderNum<br \/>\nFROM Orders\n<\/p><\/blockquote>\n<p>Will return something like this:<\/p>\n<blockquote><p>\nCost  OrderNum<br \/>\n10.00 345<br \/>\n10.00 346<br \/>\n10.00 347<br \/>\n10.00 348\n<\/p><\/blockquote>\n<p>Quick translation:<\/p>\n<p>    SUM(cost) \u2013 get me the sum of the COST column<br \/>\n    OVER \u2013 for the set of rows\u2026.<br \/>\n    () \u2013 \u2026that encompasses the entire result set.<\/p>\n<p><strong>OVER(PARTITION BY)<\/strong><\/p>\n<p>OVER, as used in our previous example, exposes the entire resultset to the aggregation\u2026\u201dCost\u201d was the sum of all [Cost]  in the resultset.  We can break up that resultset into partitions with the use of PARTITION BY:<\/p>\n<blockquote><p>\nSELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost<br \/>\n, OrderNum<br \/>\n, CustomerNo<br \/>\nFROM Orders\n<\/p><\/blockquote>\n<p>My partition is by CustomerNo \u2013 each \u201cwindow\u201d of a single customer\u2019s orders will be treated separately from each other \u201cwindow\u201d\u2026.I\u2019ll get the sum of cost for Customer 1, and then the sum for Customer 2:<\/p>\n<blockquote><p>\nCost  OrderNum   CustomerNo<br \/>\n 8.00 345        1<br \/>\n 8.00 346        1<br \/>\n 8.00 347        1<br \/>\n 2.00 348        2\n<\/p><\/blockquote>\n<p>The translation here is:<\/p>\n<p>    SUM(cost) \u2013 get me the sum of the COST column<br \/>\n    OVER \u2013 for the set of rows\u2026.<br \/>\n    (PARTITION BY CustomerNo) \u2013 \u2026that have the same CustomerNo.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Source: http:\/\/www.midnightdba.com\/Jen\/2010\/10\/tip-over-and-partition-by\/ Here\u2019s a quick summary of OVER and PARTITION BY (new in SQL 2005), for the uninitiated or forgetful\u2026 OVER OVER allows you to&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,12],"tags":[79,21,145,144,146,50,78,38,49],"class_list":["post-37620","post","type-post","status-publish","format-standard","hentry","category-how_to","category-sql","tag-microsoft","tag-ms","tag-over","tag-partition","tag-partitionby","tag-sql-2","tag-sql2008","tag-ssrs-2","tag-tsql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts\/37620"}],"collection":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/comments?post=37620"}],"version-history":[{"count":2,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts\/37620\/revisions"}],"predecessor-version":[{"id":37622,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts\/37620\/revisions\/37622"}],"wp:attachment":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/media?parent=37620"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/categories?post=37620"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/tags?post=37620"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}