CODEWITHSUNDEEP
sqldatabase
Jay Marz
Jay Marz

Reputation: 1912

How to convert a varchar into decimal but excepting one or more columns

I have some problem with excepting one or more columns when I try to convert the varchar field into decimal.my statement is as below: 

SELECT title, CAST(sumValues AS DECIMAL(9,2)
WHERE title != 'Total Clients') AS sumVal
FROM client_summary

I want to convert the sumValues field into decimal except for the column where the title is "Total Clients" (there is no decimal in counting persons). How possibly be my suitable statement for this?

Upvotes: 0

Views: 87

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

You can try this : caution, all part of a case...when...else statement must return data of the same type (or implicit conversion).

SELECT title, 
CASE WHEN title <> 'Total Clients' 
 THEN CAST( sumValues AS DECIMAL(9,2)) 
 ELSE <something else which must also be decimal> --or no else
 END AS sumVal 
FROM client_summary

Edit : if you can't find a common type, you'll have to use two columns.

SELECT title, 
    CASE WHEN title <> 'Total Clients' 
     THEN CAST( sumValues AS DECIMAL(9,2)) 
     END AS sumVal,
    sumValues as allSumValues
 FROM client_summary

Upvotes: 1

Related Questions