Insert new column that adds a series from another column

Question

I have a table with dates and follower growth for each date and want to make a new column that depicts total followers gained.

How can I insert a new column that basically adds the 'new followers' column in to a summation series?

date    | new followers
-----------------------    
1/1/15 |    1
1/2/15 |    3
1/3/15 |    5
1/4/15 |    4
1/5/15 |    3

New table would look like

date    | new followers | total followers gained
------------------------------------------------
1/1/15  |   1           |    1
1/2/15  |   3           |    4
1/3/15  |   5           |    9
1/4/15  |   4           |   13
1/5/15  |   3           |   16

sql server. question is what commands i'd need for my new column 'total followers gained' — Takeshi Tawarada, May 11 '15 at 19:24
What version of SQL Server? So know which duplicate target is best. — Martin Smith, May 11 '15 at 19:25
Why add this as a value in the table when it can be computed just as easily? I believe you're looking for a [Running total](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — xQbert, May 11 '15 at 19:25

score 0 · Answer 1 · answered May 11 '15 at 19:29

This SQL will accomplish what you want with a Select. If it is really necessary to persist this value in a table, then you could use this SQL in a trigger. But since you could just calculate this amount on the fly, I'm not sure why you would persist it.

declare @tempTable table (theDt datetime, newFollowers int)

insert into @tempTable
select '1/1/15',1
UNION select '1/2/15',3
UNION select '1/3/15',5
UNION select '1/4/15',4
UNION select '1/5/15',3

select t1.theDt, 
       t1.newFollowers + SUM(case when t2.theDt IS not null then t2.newFollowers else 0 end) as "Total Followers"
from @tempTable t1
left outer join @tempTable t2 on t1.theDt > t2.theDt
group by t1.theDt, t1.newFollowers
order by t1.theDt

Insert new column that adds a series from another column

1 Answers1