OVER and PARTITION BY - Analytic Function

I have come across a situation to sum-up a particular column of a table without using the group by clause. And below is the way i tackled it using the Analytic Function(OVER and PARTITION BY).

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, the below 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 result-set to the aggregation…”Cost” was the sum of all [Cost]  in the result-set.  We can break up that result-set 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

Quick translation :

SUM(cost) – get me the sum of the COST column

OVER – for the set of rows….

(PARTITION BY CustomerNo) – …that have the same CustomerNo.

For more Analytic Functions Click Me.

Challa.