Databases
StackOverflow2013
Postsdbo
POCalculate a Running Total in SQL Server
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCalculate a Running Total in SQL Server
    text
    copied!<p>Imagine the following table (called <code>TestTable</code>):</p> <pre><code>id somedate somevalue -- -------- --------- 45 01/Jan/09 3 23 08/Jan/09 5 12 02/Feb/09 0 77 14/Feb/09 7 39 20/Feb/09 34 33 02/Mar/09 6 </code></pre> <p>I would like a query that returns a running total in date order, like:</p> <pre><code>id somedate somevalue runningtotal -- -------- --------- ------------ 45 01/Jan/09 3 3 23 08/Jan/09 5 8 12 02/Feb/09 0 8 77 14/Feb/09 7 15 39 20/Feb/09 34 49 33 02/Mar/09 6 55 </code></pre> <p>I know there are <a href="http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx" rel="noreferrer">various ways of doing this</a> in SQL Server 2000 / 2005 / 2008.</p> <p>I am particularly interested in this sort of method that uses the aggregating-set-statement trick:</p> <pre><code>INSERT INTO @AnotherTbl(id, somedate, somevalue, runningtotal) SELECT id, somedate, somevalue, null FROM TestTable ORDER BY somedate DECLARE @RunningTotal int SET @RunningTotal = 0 UPDATE @AnotherTbl SET @RunningTotal = runningtotal = @RunningTotal + somevalue FROM @AnotherTbl </code></pre> <p>... this is very efficient but I have heard there are issues around this because you can't necessarily guarantee that the <code>UPDATE</code> statement will process the rows in the correct order. Maybe we can get some definitive answers about that issue.</p> <p>But maybe there are other ways that people can suggest?</p> <p>edit: Now with a <a href="http://sqlfiddle.com/#!3/c8880/4" rel="noreferrer">SqlFiddle</a> with the setup and the 'update trick' example above</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload