CODEWITHSUNDEEP
sqlms-access
Anton Hughes
Anton Hughes

Reputation: 1995

How to SUM two fields within an SQL query

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

Upvotes: 157

Views: 1298772

Answers (9)

Xanadu
Xanadu

Reputation: 35

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

Upvotes: 1

Barb Dawdy
Barb Dawdy

Reputation: 249

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.

Upvotes: 24

John Woo
John Woo

Reputation: 263933

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

Upvotes: 328

CelticCoder
CelticCoder

Reputation: 126

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

Upvotes: 11

E Coder
E Coder

Reputation: 305

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

Upvotes: 16

Dan Bracuk
Dan Bracuk

Reputation: 20804

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.

Upvotes: 2

LuigiEdlCarno
LuigiEdlCarno

Reputation: 2415

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.

Upvotes: 4

Obl Tobl
Obl Tobl

Reputation: 5684

Try the following:

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

Upvotes: 53

Daniel Kelley
Daniel Kelley

Reputation: 7747

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]

Upvotes: 7

Related Questions