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

8 Answers

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

0

Try the following:

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

2

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.

1

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

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]

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

0

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.

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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy