Hi Friends,

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 rowNum
    from    saledetails        
) As T1
where T1.rowNum =  1

Comments

Leave a comment

Tell us about yourself

Your Name (required field)

Your Email Address (required field)

Website URL

Comment and preferences

Your Comment

Subscribe to this comment thread

Please enter the text you see in the picture:

Submit