CODEWITHSUNDEEP
sqlms-accesssum
user3166621
user3166621

Reputation: 21

SQL SUM + Not working as Should

I have a access db hooked up to a classic asp page. It should add the value from 2 columns however rather than adding it is joining the data together. Ie 1 + 1 is showing as 11 not 2

SELECT (desc_1_amnt + desc_2_amnt) AS Sum
FROM Sales_data

If I use the - sign or the * sign it work fine. Just does not seem to add up.

Upvotes: 2

Views: 109

Answers (2)

HansUp
HansUp

Reputation: 97101

The + operator adds numeric values but concatenates string values.

Here is an example from the Immediate window.

? 1 + 1
 2 
? "1" + "1"
11

The likely explanation is that your desc_1_amnt and desc_2_amnt fields are text datatype. In that case you can cast the text values to numbers before adding them.

SELECT (Val(desc_1_amnt) + Val(desc_2_amnt)) AS [Sum]

Upvotes: 1

Paul Williams
Paul Williams

Reputation: 17020

The + operator can either be an arithmetic addition or string concatenation. From your question, it sounds like the "11" result means that the data types are some sort of text instead of numeric.

Changing the columns to a numeric type should give you the results you expect. Alternately, you could convert the text to numbers using CAST or CONVERT.

Upvotes: 1

Related Questions