If you’re getting SQL Server error Msg 8117 with the message Operand data type varchar is invalid for sum operator, it’s because you’re passing the wrong data type to an operator or function.

In this case, the error indicates that we’re passing a string to the SUM() function. The SUM() function does not operate on strings. It only works on numeric types.

The same error (Msg 8117) can also occur in other contexts – it’s not limited to the SUM() function.

Example of the Error

Here’s an example of code that produces the error:

SELECT SUM(ProductName) 
FROM Products;

Result:

Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for sum operator.

In this case, we’re trying to add up the ProductName column.

In this case, it’s highly likely that the ProductName column is a varchar column. We’ve probably got the wrong column.

Solution 1

To fix this error, we should first check that we’ve got the correct column. If we don’t have the correct column, change it to the correct column:

SELECT SUM(Price) 
FROM Products;

Hopefully that solves the issue. In other words, hopefully the Price column is numeric, like it should be.

But what if it isn’t?

Solution 2

In some cases you may find that you’ve got the correct column, but that column uses an inappropriate data type. For example, suppose our Price column was actually defined as a varchar column.

In that case, we’d get the same error:

SELECT SUM(Price) 
FROM Products;

Result:

Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for sum operator.

Upon first glance, nothing appears to be wrong with this statement. All we’re doing is getting a total of the values in the Price column. This is a perfect example of what the SUM() function was designed to do.

Of course, the assumption here is that the Price column is numeric. But according to the error message, it’s not numeric – it’s a varchar.

Let’s check the column’s data type:

SELECT
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH, 
    CHARACTER_OCTET_LENGTH AS OCTET_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Products' 
AND COLUMN_NAME = 'Price';

Result:

+-------------+--------------+----------------+
| DATA_TYPE   | MAX_LENGTH   | OCTET_LENGTH   |
|-------------+--------------+----------------|
| varchar     | 255          | 255            |
+-------------+--------------+----------------+

As suspected, the column is of type varchar.

In this case, we have two options; change the column’s type, or convert its type on the fly when getting its sum.

Let’s convert its type on the fly:

SELECT SUM(CAST(Price AS decimal(8,2))) 
FROM Products;

Result:

48.25

This worked, fortunately.

In this case, all data in the Price column can be converted to a numeric type.

If you get error Msg 8114 that reads something like Error converting data type varchar to numeric, then it means that the column contains data that can’t be converted to numeric.

The error looks this:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

In this case, you’ll need to find the non-numeric data and decide what to do with it.

Here’s how we can find the non-numeric values:

SELECT Price
FROM Products
WHERE ISNUMERIC(Price) <> 1;

Result:

+-------------+
| Price       |
|-------------|
| Ten dollars |
| Fifteen     |
+-------------+

We’ve found the culprits!

Unless there’s a good reason not to, we should change those values to their numeric equivalents.

After that, we should consider changing the column’s data type, so that this type of data can’t be inserted in the future. Doing this will help enforce data integrity.

One thing to be mindful of when using the ISNUMERIC() function is that it can sometimes return false positives. What I mean is that there are some non-numeric characters that are interpreted as numeric. See Non-Number Characters that Return Positive when using ISNUMERIC() for more about this.