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