Varchar invalid for sum operator

Question

Post reply

Varchar invalid for sum operator

Admingod

SSCertifiable

Points: 5997
More actions
October 29, 2015 at 4:35 pm

#399246

Below doesn't work for varchar column, Any ideas?
select sum(cast(Enter_your_field_name as int)) from Table_name
THanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Worked fine in my test. Maybe I'm using a different version. What does this return on your instance?

CREATE TABLE #Temp (

AVarcharColumn VARCHAR(20)

)

INSERT INTO #Temp

(AVarcharColumn)

VALUES ('0')

GO

SELECT SUM(CAST(AVarcharColumn AS INT)) FROM #Temp

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Admingod SSCertifiable Points: 5997 More actions · Answer 2

I am running on 2008 SQL Server enterprise. Below is what i am getting

Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 3

Try this:

select sum(cast(cast(replace(Enter_your_field_name, ',', '') as decimal(28, 6) as int)) from Table_name

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Admingod (10/29/2015)
I am running on 2008 SQL Server enterprise. Below is what i am getting
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

You've got values in the string column that are too large to convert to int. Try CAST .. AS BIGINT, or use a DECIMAL that's large enough to hold the values once converted to a numeric type.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

Quick question - Why are you storing numeric values in a string column?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions