CODEWITHSUNDEEP
sqlsql-servercase
sam smith
sam smith

Reputation: 107

CAST, SUM, CASE Issues

I am trying to accommodate for some rogue values in my database, that contain the string 'unknown', I want to set these to 0 and then sum the rest. But for some reason, this isnt happening. Here is what I have -

Values - VARCHAR(30) - 

     3
     0.1
     2
     16
     2
     5
     2
     Unknown
     2.4
     7
     Unknown

And here is my Cast,Sum,Case

Cast(sum(case when stake = 'Unknown' then 0 else stake end) as float) as totalStake

But I get this error - Conversion failed when converting the varchar value '0.1' to data type int.

Help!

Thanks

Upvotes: 2

Views: 8597

Answers (5)

Srikar mogaliraju
Srikar mogaliraju

Reputation: 215

Initial step would be to replace the 'Unknown' string with 0 using a replace function and then convert the column datatype to the one which allows to perform Aggregate functions and then perform SUM on top of that. The below query will work only for 'unknown' string, if you have different strings other than 'unknown' you might have to choose a different approach like using IsNumeric in Replace function and update the string value to 0. 

select sum(cast((REPLACE(stake,'unknown',0))  as float)) from table

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try the following query using isnumeric() to check numeric data.

create table temp (stake VARCHAR(30))
insert into temp values
('3'), ('0.1'), ('2'), ('16'), ('2'), ('5'), ('2'), ('Unknown'), ('2.4'), ('7'), ('Unknown')

--Select * from temp

Select sum(Cast(stake as Float)) from temp where isnumeric(stake) = 1

To handle some exception like null values or . values only you can try this

Select SUM(TRY_CAST(stake as Float)) from temp

You can find the live demo Here.

Upvotes: 1

Bob Dubke
Bob Dubke

Reputation: 66

This happens because SQL has some problems while converting decimal values to integer values. In facts, function sum returns integer values

enter image description here

I solved it using round function on the values1 variable ( sorry for using same name for table and column ):

 select Cast(sum(case when values1 = 'Unknown' then 0 else round(values1, 2) end)  as 
 float)as totalstrike
 from values1

Upvotes: 0

forpas
forpas

Reputation: 164069

You must cast stake as a float:

sum(case when stake = 'Unknown' then 0.0 else cast(stake as float) end) as totalStake

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You should explicitly convert to some sort of numeric values. Try this:

sum(try_convert(numeric(18, 4), stake)) as totalStake

Your code has at least two issues. First, your case expression returns an integer (because the first then has an integer). So, it tries to convert stake to an integer, which can generate an error.

Second, you should be doing arithmetic operations on data that is explicitly some sort of number type and not rely on implicit conversion.

Upvotes: 1

Related Questions