如果有varchar类型的列(权重)，并且数据是数字、null或NA，如何在sql中添加case条件来计算sum

Question

select stick,sum(convert(float,weight)) as total
from lod_description group by stick  

上面的查询工作正常，但当null或NA作为数据出现在权重字段中时，它会给出错误，因为varchar到int是不允许的

预期结果：

stick  | total 
oldstick 10.01 
newstick 20.034

Answer 1

理想情况下，weight列应该是数字，而不是文本。如果weight是非数字字符串，则可以求和一个返回0的CASE表达式。

SELECT stick,
SUM(CASE WHEN weight REGEXP '^[0-9]+(.[0-9]+)?$'
THEN CAST(weight AS DECIMAL(10, 6)) ELSE 0.0 END) AS total
FROM lod_description
GROUP BY stick;

Answer 2

假设当weight为NULL或='NA'时，OP的当前查询适用于以外的所有，那么：

select stick,sum(convert(float,weight)) as total
from   lod_description
where  weight is not NULL
and    weight != 'NA'
group by stick 

如果这是SybaseASE、SQLAnywhere或IQ:

select stick,sum(convert(float,weight)) as total
from   lod_description
where  isnumeric(weight) = 1
group by stick

注意：我不知道SybaseAdvantage是否具有类似的功能，但查看相关的参考手册应该不会太难。

如果使用case:设置OP

select stick,
sum(case when isnumeric(weight)=1
then convert(float,weight)
else 0.00
end) as total
from   lod_description
group by stick
-- or
select stick,
sum(case when weight is NULL or weight = 'NA'
then 0.00
else convert(float,weight)
end) as total
from   lod_description
group by stick