CODEWITHSUNDEEP
sql-servert-sqlsuminsert-update
SQL noob
SQL noob

Reputation: 31

Populating a column with calculated values from another column in T-SQL

I have a temporary table (#Temptable3) I'm populating to assist building a dataset to report from. I've solved most logical steps through a bit of trial and error and finding answers to previous questions here (thanks!) however cannot crack this last bit. To simplify, I've removed irrelevant columns from a sample dataset below:

I currently have:

RowNumber   Increment   Score
-----------------------------
    1             1     NULL
    2        100000     NULL
    3            -1     NULL
    4             1     NULL
    5            10     NULL
    6            -1     NULL
    7       -100000     NULL
    8           -10     NULL

What I'm aiming to get is the score column to populate with the Sum of the Increment column up to and including it's own row e.g.:

RowNumber   Increment   Score
-----------------------------
    1          1            1
    2     100000       100001
    3         -1       100000
    4          1       100001
    5         10       100011
    6         -1       100010
    7    -100000           10
    8        -10            0

I've tried and failed to get an various update statements to work, playing with self joins, but cannot find anything that looks promising. Apologies if this isn't enough info. Please ask questions if required Thanks for all help.

Thanks to HABO for the pointer to help me find questions on Running sums. A link in janderssons reply to a previous question lead me to a solution that worked for me:

declare @runningtotal int set @runningtotal = 0

update #TempTable3 set @runningtotal = Score = @runningtotal + Increment

from #TempTable3

Upvotes: 2

Views: 928

Answers (3)

HoneyBadger
HoneyBadger

Reputation: 15150

Something like:

SELECT  [RowNumber], Increment
      , SUM(Increment) OVER(ORDER BY [RowNumber]) AS Score
FROM    Your_Table

Should do the trick. See here.

For future readers: Apparently the over syntax is available in sql server 2012, but not the sum() over(). For an alternative solution (and a more complete solution for an update) see the answer of @JohnCappelletti.

Upvotes: 2

SQL noob
SQL noob

Reputation: 31

Thanks for the pointer from HABO to help me find previous questions on Running sums. A link in janderssons reply to a previous question lead me to a solution that worked for me:

declare @runningtotal int set @runningtotal = 0

update #TempTable3 set @runningtotal = Score = @runningtotal + Increment

from #TempTable3

Thanks again to HoneyBadger JohnCappelletti for persevering with me, I'm sure the fault was probably mine.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

Declare @Table table (RowNumber int,Increment int,score int)
Insert into @Table values
(1,1,NULL),
(2,100000,NULL ),
(3,-1,NULL), 
(4,1,NULL), 
(5,10,NULL), 
(6,-1,NULL), 
(7,-100000,NULL),
(8,-10,NULL)

Update @Table Set Score=B.Score
 From  @Table A 
 Join (Select RowNumber,Score=sum(Increment) over (order by RowNumber) from @Table) B
  on  A.RowNumber=B.RowNumber

Select * from @Table

OR

UPDATE @Table SET Score=(SELECT SUM(Increment) 
                          FROM  @Table B 
                          WHERE  b.RowNumber <= A.RowNumber)
FROM @Table A

Returns

  RowNumber Increment       score
    1           1           1
    2           100000      100001
    3           -1          100000
    4           1           100001
    5           10          100011
    6           -1          100010
    7           -100000     10
    8           -10         0

Upvotes: 2

Related Questions