Over Clause: Calculating an Aggregate of Column and Entire Record Set Without Using Group Clause

Today, I have seen one interesting function of sql server. Most of people know about it who are familiar with sql server but I was not knowing about it and I am very surprised by over() function. Over() function returns aggregate function result without using group by clause. It is built in function of sql server. If you want to create total column with entire data set then use over() function without use any parameter. We can use aggregate function with over().

Syntax:

[code:sql]
OVER ( [ PARTITION BY value_expression , … [ n ] ]
<ORDER BY_Clause> )
[/code]

1. over() returns total sum of saledetails table as per saleid.

[code:sql]
select saleId,
saledetailId,
SUM(total_Price) over() As ‘sum’
from saledetails
[/code]

2. over(PARTITION BY colx) returns more than one row i.e. as per detail records of saleId sum as per saleid.

[code:sql]
select saleId,
saledetailid,
SUM(total_Price) over(partition by saleid) As ‘sum’
from saledetails[/code]

* If you need one row as per saleid then use below query it may be helpful for you.

[code:sql]
select * from
(
select saleId,
SUM(total_Price) over(partition by saleid) As ‘sum’,
ROW_NUMBER() over(partition by saleid order by saleid desc) As rowNum
from saledetails
) As T1
where T1.rowNum = 1[/code]