Did you know that a new feature in SQL Server 2005 allows you to specify an OVER partition for aggregate functions in your SELECT statement?
For example:
select
customerID, productID, orderDate, orderAmount,
sum(orderAmount) OVER (Partition by CustomerID) as CustTotal
from
Orders
customerID productID orderDate orderAmount CustTotal
----------- ----------- ----------------------- ------------- ---------
1 1 2007-01-01 00:00:00.000 20.00 91.00
1 2 2007-01-02 00:00:00.000 30.00 91.00
1 2 2007-01-05 00:00:00.000 23.00 91.00
1 3 2007-01-04 00:00:00.000 18.00 91.00
2 1 2007-01-03 00:00:00.000 74.00 118.00
2 1 2007-01-06 00:00:00.000 34.00 118.00
2 2 2007-01-08 00:00:00.000 10.00 118.00
(7 row(s) affected)
Notice that the Total column contains the total order amount for the customer.
For more on this and some ideas on how to use this feature, check out my latest
SQLTeam article.
One use for this that I didn't mention in my article is that this can be useful for sorting. For example, suppose we need a report that shows all customers and their order details, but sorted so that customers with the highest order amounts are shown first. By using a partitioned aggregate function (along with a
common table expression or a derived table), this becomes very easy:
with CustOrders as
(
select
customerID, productID, orderDate, orderAmount,
sum(orderAmount) OVER (Partition by CustomerID) as CustTotal
from
Orders
)
select * from CustOrders order by CustTotal DESC
Without using this new feature, a join back to the Orders table or a correlated subquery to it would be required. I don't think you get any performance gains from using this technique, but it is easier and shorter to write.
Article link.
see also: