CODEWITHSUNDEEP
sqlsql-server
Amr Kamal
Amr Kamal

Reputation: 137

How to update SQL Server table with sum of 3 columns

Simply I have table called ForecastTotal, I want to sum 3 columns which may have null values and put the result in TotalAchievementVol

For example

ForecastTotal:

    OVERSEASVol  UCPVol        IBNSINAVol 
    -------------------------------------
    1              3             4
    1              2             1
    2              null          3
    3              1             null

I used this query 

Update ForecastTotal 
Set TotalAchievementVol = (SUM(ISNULL(OVERSEASVol, 0)) + 
                           SUM(ISNULL(UCPVol, 0)) + 
                           SUM(ISNULL(IBNSINAVol, 0)))

but I get an error

Msg 157, Level 15, State 1, Line 17
An aggregate may not appear in the set list of an UPDATE statement.

Any help?

Upvotes: 0

Views: 358

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I don't think you want sum(). It is an aggregation function that adds values across multiple rows. You just want to add values within a row, so the appropriate method is simply +:

Update ForecastTotal
    set TotalAcheivmentVol = COALESCE(OVERSEASVol, 0)) + COALESCE(UCPVol, 0)) + COALESCE(IBNSINAVol, 0);

Note: I prefer the ANSI standard COALESCE() to ISNULL().

However, I would not implement this functionality using an actual column. Just use a computed column:

alter table ForecastTotal
    add TotalAcheivmentVol as ( COALESCE(OVERSEASVol, 0)) + COALESCE(UCPVol, 0)) + COALESCE(IBNSINAVol, 0) );

The values are calculated when you query the table, so they are always up-to-date.

Upvotes: 2

Related Questions