Jan 4, 2013

OVER clause in aggregate function in sql server


We can use OVER clause in aggregate functions like COUNT, AVG, SUM, MAX, MIN etc.

For example:

We are creating a table tblOver and inserting some records into it:

CREATE TABLE tblOver(A BIGINT IDENTITY , B MONEY, C INT)

INSERT tblOver VALUES(10,1),(20,1),(30,1),(40,2),(50,2),(100,3)

Example 1:

SELECT
*,
SUM(B) OVER(PARTITION BY C) AS [Sum]
FROM tblOver

Output:

A B C Sum
1 10.00 1 60.00
2 20.00 1 60.00
3 30.00 1 60.00
4 40.00 2 90.00
5 50.00 2 90.00
6 100.00 3 100.00

Here meaning of PARTITION BY C is sum in the each partition according to field C:

Rows WHERE C = 1, Sum = (10.00 + 20.00 + 30.00) = 60.00
Rows WHERE C = 2, Sum = (40.00 + 50.00 ) = 90.00
Rows WHERE C = 3, Sum = 100.00

Example 2:

SELECT
*,
SUM(B) OVER(ORDER BY C) AS [Sum]
FROM tblOver

Output:

A B C Sum
1 10.00 1 60.00
2 20.00 1 60.00
3 30.00 1 60.00
4 40.00 2 150.00
5 50.00 2 150.00
6 100.00 3 250.00

Here meaning of ORDER BY C is sum of each group accord to field C in ascending from beginning:

Rows WHERE C = 1, Sum = (10.00 + 20.00 + 30.00) = 60.00
Rows WHERE C = 2, Sum = ((10.00 + 20.00 + 30.00) + (40.00 + 50.00)) = 150.00
Rows WHERE C = 3, Sum = ((10.00 + 20.00 + 30.00) + (40.00 + 50.00) + (100.00)) = 250.00

Example 3:

SELECT
*,
SUM(B) OVER(PARTITION BY C ORDER BY B) AS [Sum]
FROM tblOver

Output:

A B C Sum
1 10.00 1 10.00
2 20.00 1 30.00
3 30.00 1 60.00
4 40.00 2 40.00
5 50.00 2 90.00
6 100.00 3 100.00

Here meaning of PARTITION BY C ORDER BY B:

Rows WHERE C = 1 and B = 10.0 , Sum = 10.00
Rows WHERE C = 1 and B = 20.0 , Sum = (10.00 + 20.00) = 30.00
Rows WHERE C = 1 and B = 30.0 , Sum = (10.00 + 20.00 + 30.00) = 60.00
Rows WHERE C = 2 and B = 40.0 , Sum = 40.00
Rows WHERE C = 2 and B = 50.0 , Sum = (40.00 + 50.00) = 90.00
Labels:

1 comment:

Subscribe to: Post Comments (Atom)