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

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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