ROUND function

[code green]

I wish to ROUND a calculation to one decimal place without any trailing zeroes.
I thought this would be simple. I am using

CASE WHEN retail > 0 
THEN ROUND((retail - wholesale)/retail,1)
ELSE 0 END profit,

But I am getting results like 0.2000000000000 0001
and 0.2999999999999 9999.
Can anybody explain why and how to return only 0.20 and 0.29

[ck9663]

I wish to ROUND a calculation to one decimal place without any trailing zeroes.
I thought this would be simple. I am using

CASE WHEN retail > 0 
THEN ROUND((retail - wholesale)/retail,1)
ELSE 0 END profit,

But I am getting results like 0.2000000000000 0001
and 0.2999999999999 9999.
Can anybody explain why and how to return only 0.20 and 0.29

Try putting the CASE inside the ROUND. Something like

round(
CASE WHEN retail > 0
THEN (retail - wholesale)/retail
ELSE 0 END
,1) profit

-- CK

[code green]

I have used CAST

CASE WHEN retail > 0 
THEN CAST(ROUND((retail - wholesale)/retail,1) AS DECIMAL(5,1))
ELSE 0 END profit,