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
    Moderator

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
    Moderator
  • Cumulative sum or running total is a sequence of partial sums of a given sequence which is used to display the total sum of data as it grows with time or any other series or progression. In SQL Server, getting running totals in T-SQL is not hard task because there are different ways of calculating cumulative sums or running totals. 
    http://www.sql-datatools.com/2016/12/cumulative-sum-in-sql-server.html
    Friday, December 09, 2016 7:31 AM
  • Try using the OVER clause with ROWS UNBOUNDED PRECEDING.

    This is how I tested :

    create table #Temp1
    (
    acct varchar(10),
    Balance int
    )

    insert into #Temp1 values ('A100',100)
    insert into #Temp1 values ('A101',300)
    insert into #Temp1 values ('A102',150)
    insert into #Temp1 values ('A103',200)
    insert into #Temp1 values ('A104',900)
    insert into #Temp1 values ('A105',600)
    insert into #Temp1 values ('A106',275)
    select acct, Balance, sum(Balance) over (order by acct rows unbounded preceding ) as CUMTotal from #Temp1

    output :

    acct Balance CUMTotal
    A100 100 100
    A101 300 400
    A102 150 550
    A103 200 750
    A104 900 1650
    A105 600 2250
    A106 275 2525


    ApoorvaW

    Friday, December 09, 2016 11:15 AM