No nvarchar: "Operand data type nvarchar is invalid for sum operator"

Question

I have a view that returns ProductName, Qty, and other columns

Defined as:

SELECT ProductName, Qty, /* etc. */ 
FROM SomeConcreteTable

SomeConcreteTable defines ProductName as nvarchar(40) and Qty as float.

SELECT ProductName, SUM(Qty) 
FROM MyView
GROUP BY ProductName

This causes an error

Operand data type nvarchar is invalid for sum operator

When the view returns no rows, there is no error when the view's WHERE conditions are modified so that it returns rows.

SELECT ProductName, SUM(COALESCE(vStarts.LotQty, 0))
FROM MyView
GROUP BY ProductName

Works regardless of whether the view returns rows.

In this post, Lieven Keersmaekers says that use of an anonymous type in a query had the same issue because the type count not be inferred.

Makes sense.

However, in my case the types should not be inferred -- they are from a table.

Not to ask such an often abused question, but is this a bug?

It seems unexpected behavior that a summation across no rows would yield this error when the summation is against a nullable float (and not a varchar).

Microsoft SQL Server 2012 (SP1) - 11.0.3482.0 (X64)

Answer 1

I resolved the issue but did not find the cause. In the "try random things" phase of troubleshooting, I opened the view in SSMS and changed the FROM line to use explicit Database.Schema.Table notation. No more error even without the previously required COALESCE. I changed back to the implicit syntax. Still works. :shrug: Any ideas?

Answer 2

It is likely that your view definition for MyView CASTs or CONVERTs the Qty from a FLOAT to an NVARCHAR. You may have to CAST/CONVERT it back to SUM.

Answer 3

you should cast your column first and it will work

SELECT SUM(CAST(Qty as int)) // change to desired type, int for example purposes only

Answer 4

Try this:

SUM(convert(float,column_name))

Answer 5

I had the same problem and the solution was very simple:
Sum(0+Qty)