how to add case condition in sql to calculate sum if there is column(weight) of type varchar and data is numeric or null or NA

Question

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

above query works fine but when null or NA comes in the weight field as data it gives error as varchar to int not allowed

Expected Result:

 stick  | total 
oldstick 10.01 
newstick 20.034

Answer 1

Ideally your weight column should be numeric, and not text. You could sum a CASE expression which returns 0 in the event that weight be a non numeric string.

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

Assuming OP's current query works for everything except when weight is NULL or ='NA', how about:

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

If this is Sybase ASE, SQLAnywhere or IQ:

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

NOTE: I don't know if Sybase Advantage has a comparable function but shouldn't be too hard to review the associated reference manual to find out.

And if OP is set on using case:

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