title: Calculate a Running Total in SQL Server
body: <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="https://web.archive.org/web/20200212211219/http://geekswithblogs.net:80/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx" rel="nofollow 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="nofollow noreferrer">SqlFiddle</a> with the setup and the 'update trick' example above</p>