I have often wanted to get the actual detail columns in a group by, that weren’t being grouped.. this is how

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.

—
Now 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

Just a place to track tricks and tips I’ve used.

Just used 2 handy ones(Excel 2003):

To identify a unique list of values from a column Choose Data->Filter->Advanced Filter
In the list range, choose the cells to check.
Click the “unique records only” box.
If you want to copy the unique list somewhere, choose “copy to another location” and select that location.

To look for intersection between two columns:
=IF(ISNA(MATCH(D2,E:E,0)),””,”X”)
D2 is the first cell to check. E:E means we are looking for dups in column E.
an X will show up if D2 is found in column E.
Copy the formula down as far as you want to check column D.