CODEWITHSUNDEEP
sql-server
user3359779
user3359779

Reputation: 65

sum column values in a row and save into another column

I am trying to add together the values in separate columns in a row and save the result in another column within that same row. I want this for each row in the table.

Insert into DataTable (Total) values 
((select (T1+T2+T3+T4+T5) from DataTable))

I get an error message:

Subquery returned more than one value.

I can guess that my Select statement returns multiple values and is then attempting to store the values returned into a single row in the table.

Is there a way to achieve this?

Upvotes: 3

Views: 2700

Answers (4)

DavidG
DavidG

Reputation: 118937

Do it like this:

UPDATE DataTable
SET Total = T1+T2+T3+T4+T5

However, you can get the server to calculate this automatically using a computed column. Remove the Total column and add the replacement like this:

ALTER TABLE DataTable DROP COLUMN Total

ALTER TABLE DataTable ADD Total as (T1+T2+T3+T4+T5)

Upvotes: 3

RyanB
RyanB

Reputation: 757

If you want to put the value into a column in the same row then you need to do something like

UPDATE DataTable SET Total = T1 + T2

But if you want Total to always be the sum of other columns you should make it a computed column.

http://technet.microsoft.com/en-us/library/ms191250%28v=sql.105%29.aspx

Upvotes: 1

squillman
squillman

Reputation: 13641

UPDATE DataTable
SET Total = T1+T2+T3+T4+T5

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

The aggregate (Total) column will already have to exist:

UPDATE DataTable
SET Total = field1 + field2 + field3 + field4

Upvotes: 1

Related Questions