Contact Us

Let us know to help you out quickly!

 Bharat Patel     Feb,08 2017

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:

OVER ( [ PARTITION BY value_expression , ... [ n ] ]<ORDER BY_Clause> )

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

select saleId,saledetailId,SUM(total_Price) over() As 'sum'from saledetails

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

select saleId,saledetailid,SUM(total_Price) over(partition by saleid) As 'sum'from saledetails

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

select * from(select saleId,SUM(total_Price) over(partition by saleid) As 'sum',ROW_NUMBER() over(partition by saleid order by saleid desc) As rowNumfrom saledetails) As T1where T1.rowNum = 1