I have often wanted to get the actual detail columns in a group by, that weren’t being grouped.. this is how<\/p>\n
Here\u2019s a quick summary of OVER and PARTITION BY (new in SQL 2005), for the uninitiated or forgetful\u2026<\/p>\n
OVER
\nOVER 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
SELECT SUM(Cost) OVER () AS Cost
\n, OrderNum
\nFROM Orders<\/p>\n
Will return something like this:<\/p>\n
Cost OrderNum
\n10.00 345
\n10.00 346
\n10.00 347
\n10.00 348<\/p>\n
Quick translation:<\/p>\n
\u2022SUM(cost) \u2013 get me the sum of the COST column
\n\u2022OVER \u2013 for the set of rows\u2026.
\n\u2022() \u2013 \u2026that encompasses the entire result set.
\nOVER(PARTITION BY)
\nOVER, 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
SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
\n, OrderNum
\n, CustomerNo
\nFROM Orders<\/p>\n
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
Cost OrderNum CustomerNo
\n 8.00 345 1
\n 8.00 346 1
\n 8.00 347 1
\n 2.00 348 2<\/p>\n
The translation here is:<\/p>\n
\u2022SUM(cost) \u2013 get me the sum of the COST column
\n\u2022OVER \u2013 for the set of rows\u2026.
\n\u2022(PARTITION BY CustomerNo) \u2013 \u2026that have the same CustomerNo.<\/p>\n
—
\nNow I also wanted to perform what would be a ‘HAVING’ clause on the group by.. couldn’t find anything yet, so just put a wrapper select statement with the added where clause<\/p>\n","protected":false},"excerpt":{"rendered":"
I have often wanted to get the actual detail columns in a group by, that weren’t being grouped.. this is how Here\u2019s a quick summary of OVER and PARTITION BY (new in SQL 2005), for the uninitiated or forgetful\u2026 OVER … Continue reading