SQL Varchar 对 Sum 运算符无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9673327/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Varchar invalid for Sum operator
提问by peter
I have a table called Cos and the datatype of Amt is Float and sample data looks like:
我有一个名为 Cos 的表，Amt 的数据类型是 Float，示例数据如下所示：
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?
谁能帮我？
回答by Icarus
Try doing this:
尝试这样做：
Select Acct,Period,F_year, Sum(isnull(cast(AMT as float),0)) as Amt
from dbo.Cos
Group By Acct,Period,F_year
回答by Jonathan Wood
Apparently, the value "1669.57" is a string. So what does it mean to add this value to another?
显然，值“1669.57”是一个字符串。那么将这个值添加到另一个值是什么意思呢？
The error message is correct: It's not valid to add text values together. If it was valid, I could not tell what the result should be.
错误消息是正确的：将文本值添加在一起是无效的。如果它是有效的，我不知道结果应该是什么。
You should either change your column type to a numeric type, or convert it somehow before trying to add it.
您应该将列类型更改为数字类型，或者在尝试添加之前以某种方式将其转换。
回答by Maxx
If Amtis intended to be used for mathematical operations, then it should be type Decimaland not varchar.
如果Amt打算用于数学运算，那么它应该是 typeDecimal而不是varchar。
