Can I sum the total value of 3 columns in a data extension?

Question

I have 3 Fields showing 'number of rewards' available that are decimal inputs.

Is there a way I can display a sum of the 3 columns in a field in a data extension so that number can be used in email personalisation?

EG:

Field a - 2
Field b - 6
Field c - 1

Sum
Total - 8

Alternatively, is this possible with AMP script?

also, is it possible to combine with a Lookup if data is stored in a separate table and linked through a contact id?

EG - %%[set @contactId =[ContactId]]%% %%=Lookup('othertable','field1','ContactID',@contactId)=%%

Something like:

%%[set @contactId =[ContactId]]%% %%[set @field1 =[%%=Lookup('othertable','field1','ContactID',@contactId)=%%] %%[set @field2 =[%%=Lookup('othertable','field1','ContactID',@contactId)=%%] %%[ SET @total = Add(field1,field2) ]%%

disclaimer - this doesn't work, but an example of what I'm trying to pull off!

Answer 1

Yep, this is possible via ampscript and SQL. For SQL you would need to run a query as a update. This should work:

SELECT (field1+field2+field3) AS TotalSum 
FROM TableName

Ampscript solution using the Add function (assuming field1 and field 2 are in sendable DE)

%%[
SET @total = Add(field1,field2)
SET @total = Add(@total,field3)
]%%

Rounding to the nearest whole number can be achieved using the FormatNumber function in Ampscript

%%=FormatNumber(@total,"N")=%%

Answer 2

You can use the Add function in AMPscript and Subtract function.

    %%[ 
         set @Avalue = Subtract(@a,2) 
         set @Bvalue = Subtract(@b,6)
         set @Cvalue = Subtract(@c,1)
         set @sum = Add(@Avalue, @Bvalue)
         set @sum2 = Add(@sum, @Cvalue)
    ]%%