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

Question

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

What you're looking for is called a _running sum_. Try a little more searching, e.g. `[tsql] running sum`. — HABO, Jun 29 '16 at 13:27

HoneyBadger · Answer 1 · 2016-06-29T18:26:44.087

2

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.

edited Jun 29 '16 at 18:26

answered Jun 29 '16 at 12:10

HoneyBadger

14,750
3
34
48

IMHO - A comment should be required to down vote - even if left as anonymous – John Cappelletti Jun 29 '16 at 12:32
Hi HoneyBadger, thanks for the reply. Like John Cappelletti's solution I also got an "Incorrect syntax near 'order'." message when attempting yours? – SQL noob Jun 29 '16 at 13:17
Your version of sql server should support this syntax. Can you tell me more about the error message? – HoneyBadger Jun 29 '16 at 13:29
The error message in full is "Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'order'." - Nothing more to add that I can think of - sorry. – SQL noob Jun 29 '16 at 13:44
Can you put rownumber in brackets? ([]) (see edit) rownumber is also a function... – HoneyBadger Jun 29 '16 at 14:13
Still no change in the error message i'm afraid. :-( – SQL noob Jun 29 '16 at 14:25

John Cappelletti · Answer 2 · 2016-06-29T14:20:12.520

2

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

edited Jun 29 '16 at 14:20

answered Jun 29 '16 at 12:17

John Cappelletti

79,615
7
44
66

Hello John - thanks for the prompt reply, I'd tried your solution above, substituting my table name as appropriate but got the message "Incorrect syntax near 'order'." I tried pasting yours into a clean query window and ran as is and got the same message. Not sure if relevant, I'm in T-SQL? MS SQL SMS 2008 R2 – SQL noob Jun 29 '16 at 13:08
What version of SQL 2008+ ? – John Cappelletti Jun 29 '16 at 13:11
There are 3 @Table's did you do all three? – John Cappelletti Jun 29 '16 at 13:11
Version info... Microsoft SQL Server Management Studio 10.50.6000.34 Microsoft Analysis Services Client Tools 10.50.6000.34 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 4.0 6.0 Microsoft Internet Explorer 9.0.8112.16421 Microsoft .NET Framework 2.0.50727.5485 Operating System 6.1.7601 – SQL noob Jun 29 '16 at 13:18
Select @@Version will give you the version of the SQL Server – John Cappelletti Jun 29 '16 at 13:20
Can you run the script as provided? .. without replacing @Table – John Cappelletti Jun 29 '16 at 13:21
When translating to my query I didn't declare and populate as my table aready exists as '#TempTable3' with data in it. I put in the remainder from 'update' onwards, changing the '@Table' to '#TempTable3' as appropriate. Otherwise left it as written. The only other thing of note is that the intellisense has underlined the 'B.PeriodScore' as an invalid column name. – SQL noob Jun 29 '16 at 13:27
Select @@ Version gives Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) – SQL noob Jun 29 '16 at 13:28
I ran as provided in a clean query window, including the declare and insert statements and got the same "Msg 102, Level 15, State 1, Line 14 Incorrect syntax near 'order'." Thanks for continued assistance! – SQL noob Jun 29 '16 at 13:31
Please ignore the intellisense underline, that was caused by a mis-key on my part – SQL noob Jun 29 '16 at 13:34
I'm at a loss. Even went to MS to confirm the OVER clause was in 2008. – John Cappelletti Jun 29 '16 at 13:53
Fair enough and thanks for efforts - I'm currently exploring running sums as advised by HABO above. – SQL noob Jun 29 '16 at 13:55
Updated my answer see after OR – John Cappelletti Jun 29 '16 at 14:20
That works! Thanks for persevering John. I have up-voted but as a new user, don;t have a reputation score high enough for this to show yet? As an aside your solution that worked after to OR had a slower run time than the one I had found after searching with HABO's suggested search criteria. I'm still grateful though. – SQL noob Jun 29 '16 at 14:52
1

No worries. Turns out the OVER clause is supported in 2008, BUT SUM() OVER is not. Cheers – John Cappelletti Jun 29 '16 at 14:54

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

1

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 29 '16 at 14:42

SQL noob

31
5

Without specifying an explicit order, SQL Server may update the rows in any order. Solution like John Cappeletti's ensure that the sums are calculated based on the correct rows. Depending on the version of SQL Server you're using, [`LAG`](https://msdn.microsoft.com/en-us/library/hh231256.aspx?f=255&MSPPError=-2147217396) may be useful. (It helps if you tag database questions with both the software and version, e.g. `sql-server-2014`.) – HABO Jun 29 '16 at 16:41

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

3 Answers3