Query for running total in SQL Server

This will work on SQL 2000/2005/2008.

Most cumulative aggregations have constraints such as a begin and end date or grouped by customers.

Your solution is good for a single aggregation without much filtering. People will find that the query plans generated would be very similiar to that of their favorite cumulative aggregation formula in most cases.

However, if you just took the template above and tried to plug it into some report or code you're working on that does a running total plus has other calculations like row counts or averages thrown into the mix, it will quickly become a poor performing query.

That is why I gave the query a 2. It is misleading that it works in all situations.

Below you'll see the same query but self joined on an imaginary customerID column, with an additional aggregation for average amount. Doing a subquery for multiple aggregations like your template above would not be ideal.

SELECT  t1.CustomerID,
        SUM(t2.Field1) AS RunningTotal,
        CAST(AVG(1.*t2.Field1) AS DECIMAL(12, 2)) AS AvgField1
FROM    [Table] AS t1
    JOIN [Table] AS t2
      ON t2.CustomerID = t1.CustomerID
      AND t2.Field1 <= t1.Field1
GROUP BY t1.CustomerID
ORDER BY t1.CustomerID