Click here to Skip to main content
15,867,949 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
My quality column is varchar type and i want to take sum of that column but i have both text and numeric values in that field. if i run the below query i get error like below
"Conversion failed when converting the varchar value '100.00' to data type int."


SQL
select  distinct b.UserName,SUM (CONVERT(int,b.Quality)) as Amt  from table1 a, table2 b where b.UserName like b.USerName Group BY b.UserName ;


or is there any alternate way to convert the quality to numeric before finding the sum.

Please help me on this query.
Posted
Updated 9-Apr-15 18:49pm
v2

Your table is in bad shape.
Mixing numbers with strings and symbols in a single cell. It is "pain in the ass" to try to split them given the free form of values.
It is the result of a combination of mistakes - wrong field type, poor normalization, and without proper input validations.
IMHO, as it is now , it is not feasible to solve it at the SQL level as SQL is NOT meant for such things. You may want to do it at code behind but again it is 'pain in the ass'. The least option is to redesign this table. Ultimately, I would recommend revamping the whole database as I suspect the other tables are in bad shapes either.
Learn more about: Introduction to database design[^]
If you cannot do it, you should get an expert to do it for you. The database is the root of an application, if it is not designed correctly, the whole application will be a flop.
 
Share this answer
 
v4
Comments
SukirtiShetty 10-Apr-15 2:03am    
thank you. i will redesign the table for numeric values
Use CAST function
SQL
select Cast(100.00 as int)
 
Share this answer
 
Comments
SukirtiShetty 10-Apr-15 0:44am    
if i use cast(quality) it shows the same error.
Conversion failed when converting the varchar value '100.00' to data type int.
King Fisher 10-Apr-15 0:47am    
Remove the single quotation ,pass only 100.00 not '100.00'
King Fisher 10-Apr-15 0:47am    
Try my query its working Cast(quality as int)
SukirtiShetty 10-Apr-15 0:55am    
i have not used single quotation in query.
select distinct b.UserName,SUM (Cast(b.Quality as int)) as Amt from table1 a, table2 b where b.UserName like b.USerName Group BY b.UserName ;
King Fisher 10-Apr-15 1:05am    
Check your Quality Column in your Table.
In addition to solution 2 by Peter Leow[^], i'd like to propose solution with CASE WHEN... END.

SQL
SELECT SUM(NumberField)
FROM (
  SELECT CASE
        WHEN CHARINDEX('%25', PseudoNumericField) >) THEN CONVERT(INT, REPLACE(PseudoNumericField, '%25', ''))/100 
        WHEN ... THEN ... 
      END AS NumberField
) AS T


Note: % is a special character[^] and we need to use 25 to escape its special meaning ;)

See:
CASE WHEN ... END[^]
CHARINDEX[^]
REPLACE[^]
 
Share this answer
 
Hi Please find the below example.Use it if it is needed for you

SQL
create table #temp (id int Primary key Identity(1,1),quality nvarchar(50))
 insert into  #temp values ('100%')
 insert into #temp values('75%')
 insert into #temp values('75')

select * from #temp

select case when quality like '%[%]%' then cast((left (quality,len(quality)-1))as int) else cast(quality as int) end from #temp
 
Share this answer
 
Comments
SukirtiShetty 14-Apr-15 5:31am    
Its solved thank you..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900