Q
Problem solve Get help with specific problems with your technologies, process and projects.

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)

Dig Deeper on SQL-Transact SQL (T-SQL)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close