CODEWITHSUNDEEP
sqlsql-serversum
WWaldo
WWaldo

Reputation: 223

Trying to sum two columns in SQL Server results in error message

I'm trying to calculate the sum of two separate fields with the query

select sum(percent12 + percent21) as total
from finalquery

but I keep getting this error:

Msg 8117, Level 16, State 1, Line 535

Operand data type varchar is invalid for sum operator.

However, if I do:

select percent12 + percent21 as total
from finalquery

I get:

(total)
50.0040.00
25.0025.00
100.0 0.00
100.0 0.00
100.0 0.00

How can I fix this?

Upvotes: 3

Views: 5712

Answers (4)

SandPiper
SandPiper

Reputation: 2906

When you are using the + operator in your second query, you are actually concatenating two strings. You know they are strings because the error message tells you it's a varchar (i.e. a string).

Instead, you need to convert each field into a number data type and then wrap those in your aggregation. In this case, I chose to cast it as a decimal data type based off of your sample data, but you can cast as integer or other numeric data type as well.

SELECT SUM(CAST(percent12 AS DECIMAL(10, 4)) + CAST(percent21 AS DECIMAL(10, 4))) AS total
FROM finalquery

Of course, the obvious answer is to stop storing numbers as strings. If you can modify your database schema, seriously look into changing that.

Upvotes: 1

SMor
SMor

Reputation: 2862

Fix your schema and you will solve this problem and prevent many others in the future. You should choose the appropriate datatype for each column - don't just blindly select some type of string because it's "easier".

Upvotes: 0

Eli
Eli

Reputation: 2608

Both of your percentage columns are a varchar, the + operator can concatenate strings together, and that is what you're seeing.
I would suggest that you use a CAST to a NUMERIC data type, especially as it seems that you have a fixed amount of spaces after the decimal.

Try the following: 

SELECT 
    SUM(CAST(percent12 AS NUMERIC(10,2))+ CAST(percent21 AS NUMERIC(10,2))) AS total
FROM finalquery

Others have suggested that you CAST to a FLOAT, though that has known rounding errors (and known workarounds)

Float rounding error: SQL Server rounding Error, Giving different values

Upvotes: 2

Tlivanios
Tlivanios

Reputation: 31

It seems that the values you are trying to sum are varchar/string type and therefore they cannot be summed up.

Try convert them to integer or float (depending of the type) before summing

ie at SQL Server Select sum(convert(float,x1) + convert(float,x2) )

Upvotes: 1

Related Questions