- February 8, 2017
- Posted by: Bharat Patel
- Category: Uncategorized
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().
OVER ( [ PARTITION BY value_expression , … [ n ] ]
<ORDER BY_Clause> )
1. over() returns total sum of saledetails table as per saleid.
SUM(total_Price) over() As ‘sum’
2. over(PARTITION BY colx) returns more than one row i.e. as per detail records of saleId sum as per saleid.
SUM(total_Price) over(partition by saleid) As ‘sum’
* If you need one row as per saleid then use below query it may be helpful for you.
select * from
SUM(total_Price) over(partition by saleid) As ‘sum’,
ROW_NUMBER() over(partition by saleid order by saleid desc) As rowNum
) As T1
where T1.rowNum = 1[/code]