Neat! I presume this has a partition clause?

Yes it does:

DECLARE @trn TABLE (
    TrnID INT IDENTITY,
    CustID INT,
    Amount SMALLMONEY
)

INSERT INTO @trn (CustID, Amount)
SELECT 1, 100 UNION ALL
SELECT 1, 125 UNION ALL
SELECT 1, 75 UNION ALL
SELECT 2, 12 UNION ALL
SELECT 2, 45

SELECT 
    *,
    SUM(Amount) 
    	OVER (PARTITION BY CustID ORDER BY TrnID) 
    	AS Total
FROM @trn 

/*
TrnID CustID Amount  Total
----- ------ ------- -------
1     1      100.00  100.00
2     1      125.00  225.00
3     1      75.00   300.00
4     2      12.00   12.00
5     2      45.00   57.00
*/

Learned 2 things. 1. For some reason I was under the impression that we could Order By with Over clause prior to 2012. Wrong! 2. How and when to use this new enhancement from SQL Server 2012.

Thanks for the post.

This is excellent! Simple, yet powerful!

vanne040,

Prior to SQL Server 2012, you can use Order By with Over Clause. For example, in row_number() clause "Order By" is necessary. However, in aggregate functions with OVER Clause, we can't use it. Now in SQL Server 2012, we can use OrderBy in Aggregate functions with OVER clause

Thanks for correcting my comment. I forgot to add the part with ranking functions which can use Over clause.

Thanks for info! I just wonder why it take MS so long to make it possible?

Beware that omitting the ROWS/RANGE BETWEEN clause makes SQL Server default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is disk spooled. To force a memory spool, you should write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is much faster.