Sum aggregate operation won't take varchar data type
The sum or average aggregate operation cannot take a varchar data type as an argument. So how can this user sum the total? Development expert Greg Low explains.
I'm trying to execute the following query in SQL Query Analyzer:
select shipname,sum(total)from Orders where bogus='No'
and year(Date)='2004' and orderID like 'MN%'
Group by shipname
order by shipName
It gives the following error:
Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a varchar data type as an argument.
The data type of total is varchar, so how can I sum the total?
To total a value in a varchar column, you need to convert it to a type that can be totaled, eg: sum(cast(total as decimal(18,2)). However, if you've also got the possibility of invalid numeric varchar values, you'll need to check for that as well. That means something like: SUM(CASE WHEN ISNUMERIC(total) THEN CAST(total AS decimal(18,2)) ELSE 0 END)
