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
Read rest of entry