none
Add a cumulative total column to a select statement

    Question

  • I have a temp table with acct and balance; I would like to do a select statement that would add an additional column that has a cumulative total.

    SELECT

     

     acct, Balance, cumBal

    FROM #Temp1

    ORDER

     

    BY Balance desc, Acct

     

    Thank you

    Monday, November 01, 2010 9:52 PM

Answers

  • If the temp table is not too big, you can use this set based solution

    SELECT acct, Balance, TB.cumBal
    
    FROM #Temp1 T
    cross apply
    (select sum(Balance) as cumBal from #Temp1 T2
    where T1.Date<=T2.Date
    and T1.Acct = T2.Acct) TB
    ORDER BY Balance desc, Acct
    

    See also solutions from this long thread 
    Lightning Fast Hybrid RUNNING TOTAL - Can you slow it down?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Tuesday, November 09, 2010 3:27 AM
    Monday, November 01, 2010 10:10 PM

All replies

  • select t1.acct, t1.Balance, (select sum(Balance) from tb t2 where t2.acct <= t1.acct) as cumBal

    from #Temp1 t1

    order by t1.acct


    Ali Hamdar (alihamdar.com - www.ids.com.lb)
    Monday, November 01, 2010 10:09 PM
  • If the temp table is not too big, you can use this set based solution

    SELECT acct, Balance, TB.cumBal
    
    FROM #Temp1 T
    cross apply
    (select sum(Balance) as cumBal from #Temp1 T2
    where T1.Date<=T2.Date
    and T1.Acct = T2.Acct) TB
    ORDER BY Balance desc, Acct
    

    See also solutions from this long thread 
    Lightning Fast Hybrid RUNNING TOTAL - Can you slow it down?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Tuesday, November 09, 2010 3:27 AM
    Monday, November 01, 2010 10:10 PM