How to SUM two fields within an SQL query

Question

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row.

This is my code.

Sum(tbl1.fld1 + tbl1.fld2) AS [Total]

Is this what the SUM function is used for, or can you only use the SUM function for getting the total of a column?

Thanks

Persuade

Answer 1

SUM is an aggregate function. It will calculate the total for each group. + is used for calculating two or more columns in a row.

Consider this example,

ID  VALUE1  VALUE2
===================
1   1       2
1   2       2
2   3       4
2   4       5

 

SELECT  ID, SUM(VALUE1), SUM(VALUE2)
FROM    tableName
GROUP   BY ID

will result

ID, SUM(VALUE1), SUM(VALUE2)
1   3           4
2   7           9

 

SELECT  ID, VALUE1 + VALUE2
FROM    TableName

will result

ID, VALUE1 + VALUE2
1   3
1   4
2   7
2   9

 

SELECT  ID, SUM(VALUE1 + VALUE2)
FROM    tableName
GROUP   BY ID

will result

ID, SUM(VALUE1 + VALUE2)
1   7
2   16

Avestan

Answer 2

Try the following:

SELECT *, (FieldA + FieldB) AS Sum
FROM Table

Hyperbolize

converting to int could be important too, if the fields are varchars containing ints – Icebound

@Obl Tobl I want the sum of columns that is dynamic – Hussein

Answer 3

Just a reminder on adding columns. If one of the values is NULL the total of those columns becomes NULL. Thus why some posters have recommended coalesce with the second parameter being 0

I know this was an older posting but wanted to add this for completeness.

Grafting

Thanks Barb, next to coalesce one can also use ISNULL. For example, ISNULL((value_1),0) + ISNULL((value_2),0) – Homogenesis

Answer 4

ID  VALUE1  VALUE2
===================
1   1       2

1   2       2
2   3       4
2   4       5

select ID, (coalesce(VALUE1 ,0) + coalesce(VALUE2 ,0) as Total from TableName

Firry

Answer 5

Due to my reputation points being less than 50 I could not comment on or vote for E Coder's answer above. This is the best way to do it so you don't have to use the group by as I had a similar issue.
By doing SUM((coalesce(VALUE1 ,0)) + (coalesce(VALUE2 ,0))) as Total this will get you the number you want but also rid you of any error for not performing a Group By. This was my query and gave me a total count and total amount for the each dealer and then gave me a subtotal for Quality and Risky dealer loans.

SELECT 
    DISTINCT STEP1.DEALER_NBR
    ,COUNT(*) AS DLR_TOT_CNT
    ,SUM((COALESCE(DLR_QLTY,0))+(COALESCE(DLR_RISKY,0))) AS DLR_TOT_AMT
    ,COUNT(STEP1.DLR_QLTY) AS DLR_QLTY_CNT
    ,SUM(STEP1.DLR_QLTY) AS DLR_QLTY_AMT
    ,COUNT(STEP1.DLR_RISKY) AS DLR_RISKY_CNT
    ,SUM(STEP1.DLR_RISKY) AS DLR_RISKY_AMT
    FROM STEP1
    WHERE DLR_QLTY IS NOT NULL OR DLR_RISKY IS NOT NULL
        GROUP BY STEP1.DEALER_NBR

Consistence

Answer 6

SUM is used to sum the value in a column for multiple rows. You can just add your columns together:

select tblExportVertexCompliance.TotalDaysOnIncivek + tblExportVertexCompliance.IncivekDaysOtherSource AS [Total Days on Incivek]

Finalist

Answer 7

The sum function only gets the total of a column. In order to sum two values from different columns, convert the values to int and add them up using the +-Operator

Select (convert(int, col1)+convert(int, col2)) as summed from tbl1

Hope that helps.

Icebound

Answer 8

If you want to add two columns together, all you have to do is add them. Then you will get the sum of those two columns for each row returned by the query.

What your code is doing is adding the two columns together and then getting a sum of the sums. That will work, but it might not be what you are attempting to accomplish.

Ics

Answer 9

Note:
Better to use ISNULL() than COALESCE() if true/false is to be the result.
Also NULLIF() is useful.

Derain