How to SUM two fields within an SQL query
SqlMs AccessSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
Try the following:
SELECT *, (FieldA + FieldB) AS Sum
FROM Table
Solution 3 - Sql
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.
Solution 4 - Sql
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
Solution 5 - Sql
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]
Solution 6 - Sql
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
Solution 7 - Sql
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.
Solution 8 - Sql
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.