I have a query where i calculate the sum of column value but that column data type is varchar,so how should i add the sum function to that varchar column

Operand data type varchar is invalid for sum operator.

select

Sum(CASE WHEN sn.StateName  LIKE 'newyork%'
OR sn.StateName  LIKE 'London%'
THEN 'TotalStateCount'
ELSE StateName
END) as LastState

from stateCapital

here am getting the error like 

operand datatype varchar is invalied for Sum operator

can you give me the right answer to resolve this problem

• Edited by Ychinnari 21 hours 50 minutes ago

You can convert it to numerical datatype, if possible.

declare @T table 
( Id int primary key, Code varchar(10) );

insert @T 
values 
( 1, '100'), ( 2, '100'), ( 3, '100');

select sum(cast(Code as int)) as SM
from @T;

Hi

This is because VarChar is a data type so you cannot sum text strings.  What is the column you are trying to perform the numerical operation on?  does any record in this field contain any other characters apart from numerical characters? (0

sum function to that varchar column

What for a result are expecting? Do you may mean to concatenate several strings into one long string?

and i have tried this way,can you pls give me any other way

something like this?

    select

Sum(CASE WHEN sn.StateName  LIKE 'newyork%'
OR StateName  LIKE 'London%'
THEN 1
ELSE 0
END) as LastState

from stateCapital sn

--Prashanth

• Proposed as answer by Ch. Rajen SinghEditor 2 hours 47 minutes ago

ALL

THERE MAY BE AN EASIER WAY TO FIX THIS...

It may be worthwhile for Ychinnari<abbr class="affil"></abbr>  to inspect the data in the field.  If all of the data is of a numeric nature then it will be easier to alter the column to an int, float etc....

Can you change SUM to COUNT ?

create table #t (c varchar(10))

insert into #t values ('a')
insert into #t values ('a')
insert into #t values ('a')
insert into #t values ('b')
insert into #t values ('b')
insert into #t values ('c')


select sum(c) from #t where c ='b'
--Msg 8117, Level 16, State 1, Line 1
--Operand data type varchar is invalid for sum operator.
select count(c) from #t where c ='b'