# 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

• 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
```

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 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
```

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 Tuesday, November 09, 2010 3:27 AM
Monday, November 01, 2010 10:10 PM
• 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