Friday 4 February 2011

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     

0 comments:

Post a Comment

 

Popular Posts

developer-express Copyright © 2009 Black Nero Blogspot Templatesis Designed by Ipietoon Sponsored by Online Business Journal