Click here to Skip to main content
Click here to Skip to main content
Alternative Tip

Query for running total in SQL Server

, 4 Sep 2011
Rate this:
Please Sign up or sign in to vote.
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...

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

fordc03

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 4 Sep 2011
Article Copyright 2011 by fordc03
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid