Varchar is not valid for Sum operator

I have a table called Cos and the Amt datatype is Float and the sample data looks like this:

Acct  Period  F_year   Amt
Detf  1       2011     Null
Detf  2       2011     Null
Detf  3       2011     1669.57
FTE   1       2011     3205.11
FTE   2       2011     0
FTE   3       2011     Null


I wrote a query like:

Select Acct,Period,F_year, Sum(AMT) as Amt
from dbo.Cos
Group By Acct,Period,F_year
Where Amt is not null


But I am getting this error:

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


Can anyone help me?


source to share

3 answers

Try this:

Select Acct,Period,F_year, Sum(isnull(cast(AMT as float),0)) as Amt
from dbo.Cos
Group By Acct,Period,F_year




Apparently the value "1669.57" is a string. So what does it mean to add this value to another?

The error message is correct: it is not valid for adding text values ​​together. If it really was, I could not say what the result should be.

You must either change the column type to a numeric type, or convert it in some way before trying to add it.



If Amt

intended for mathematical operations, then it should be a type Decimal

, not varchar




All Articles