SQL: OVER Clause

The OVER clause allows partitioning and ordering of the rowset.

The syntax for this function:

Ranking Window Functions

< OVER_CLAUSE > :: =

    OVER ( [ PARTITION BY value_expression, ... [ n ] ]

           <ORDER BY_Clause> )Aggregate Window Functions

< OVER_CLAUSE > :: =

    OVER ( [ PARTITION BY value_expression, ... [ n ] ] )

As specified above the OVER clause can be used in conjunction with aggregate functions, which can make your code a little shorter.

For example, consider the following bookings table:

SELECT * FROM #Bookings ORDER BY CourseID

CourseID	DelegateID	BookingDate	BookingCost
1	1	2011-02-04	10.00
1	2	2011-02-04	10.00
2	1	2011-01-04	10.00
3	1	2011-03-04	50.00
3	2	2011-03-04	50.00

Now it is quite simple to return the total cost per delegate by using the SUM aggregate function with the OVER clause.

SELECT CourseID, DelegateID, BookingDate, BookingCost, SUM(BookingCost) OVER (PARTITION BY DelegateID) AS TotalCost FROM #Bookings

CourseID	DelegateID	BookingDate	BookingCost	TotalCost
1	1	2011-02-04	10.00	70.00
2	1	2011-01-04	10.00	70.00
3	1	2011-03-04	50.00	70.00
1	2	2011-02-04	10.00	60.00
3	2	2011-03-04	50.00	60.00

An alternative SQL sctipt without using the OVER clause would be:

SELECT CourseID, DelegateID, BookingDate, BookingCost,
    (SELECT SUM(BookingCost) FROM #Bookings BK WHERE BK.DelegateID = #Bookings.DelegateID) AS TotalCost
FROM #Bookings