CODEWITHSUNDEEP
sqlsql-server
dublintech
dublintech

Reputation: 17785

summing all values in each row

I have a table like:

col1 col2 col3 total
3    5    6
7    8    null
...

I want the total column to the sum of col1, col2, col3 for each for. So in the case, total should be:

col1 col2 col3 total
3    5    6    14
7    8    null 15
...

What is the quickest to up the entire total column for this?

Upvotes: 0

Views: 11648

Answers (2)

Jamiec
Jamiec

Reputation: 136174

select col1, col2, col3, 
       isnull(col1,0) + isnull(col2,0) + isnull(col3,0) as total
from tableName

If you want that column to actually form part of the table, your best bet is to make it a computed column. This stops it going out of sync with the rest of the row, while still allowing you to treat it like any other column in a select statement.

A create table statement that will do this:

CREATE TABLE [dbo].[tableName](
    [col1] [int] NULL,
    [col2] [int] NULL,
    [col3] [int] NULL,
    [total]  AS ((isnull([col1],0)+isnull([col2],0))+isnull([col3],0))
)

Alternatively, in SSMS just goto your table designer, select the total column, and paste the expression isnull([col1],0)+isnull([col2],0))+isnull([col3],0) into the box titled computed column specification > formula

Finally, if you really want to just backfill the total column in the table (Dont! Its a bad bad idea. Your data will become out of sync later when somebody updates col1,2 or 3 and forgets to update total) just use an update

UPDATE tableName
SET total = isnull([col1],0)+isnull([col2],0))+isnull([col3],0)

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 453847

Another way

SELECT *, 
       (SELECT SUM(C) 
        FROM   (VALUES(col1), 
                      (col2), 
                      (col3)) V(C)) AS [Total] 
FROM   YourTable

Upvotes: 3

Related Questions