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
Rows
WHERE C = 3 and B = 100.0 , Sum = 100.00
Stored procedure parameters in sql server
Sql server temporary stored procedures
Foreign key constraint sql server | Referential constraint
IF statement or condition in WHERE clause of SELECT statement in sql server
Sql server create unique constraint which allow multiple NULL values
Dynamic ORDER BY clause in sql server
Stored procedure parameters in sql server
Sql server temporary stored procedures
Foreign key constraint sql server | Referential constraint
IF statement or condition in WHERE clause of SELECT statement in sql server
Sql server create unique constraint which allow multiple NULL values
Dynamic ORDER BY clause in sql server
1 comment:
Good one...
Post a Comment