Tuesday, April 27, 2010

Running total in SQL Server...

Not really sure how good this is... but seems to do the job!

;with theData (rowNum, GroupA, GroupRowNum, theValue) as
   select row_number() over (order by MajorGroup, GroupOrder),
   row_number() over (partition by MajorGroup
      order by MajorGroup, GroupOrder),
   from DataTable
select X.GroupA, X.GroupRowNum, X.theValue +
      (select sum(theValue)
      from theData Y
      where Y.GroupA=X.GroupA and Y.GroupRowNum > X.GroupRowNum), 0
from theData X

For some reason I can't get an execution plan as I get an error - I've filed a Connect bug with Microsoft.


  1. Howdy!

    You might want to check the column order of the CTE with the SELECT list of that same CTE. ;-)

    You also might want to read the article I wrote on Triangular Joins on SQLServerCentral.com to see why that particular method for doing running totals is usually not a good idea.


    --Jeff Moden

  2. Good point Jeff... I can't wait for SQL Server to get enhanced analytic functions!