problem with ROUND()

Question

Post reply

problem with ROUND()

Kari Suresh

Hall of Fame

Points: 3712
More actions
July 27, 2009 at 4:22 am

#134613

Hi All,
I am not getting consistent results when using ROUND().
For example:
1. If I execute
SELECT ROUND( (150.85*100)/(200), 2)
it is giving 75.430000 as result.
2. SELECT ROUND(((150.85*100)/(200-0)), 2). Result for this is 75.4300000000000.
3.
DECLARE @A1 float, @A2 float, @A3 float, @A4 float, @A5 float
SET @A1 = 150.85
SET @A2 = 200
SET @A3 = 0
SET @A4 = 100
SELECT @A5= ROUND( ((@A1*@A4) / (@A2-@A3) ), 2)
SELECT @A5
Result is 75.42.
But, the correct result is 75.43.
Can anybody give me suggestion to get correct result?
Thanks.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

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

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

Lowell SSC Guru Points: 323507 More actions · Answer 1

it looks like all you want to do is remove the extra zeros.

to do that, you need to decide on the precision of your @A5 variable.

here's your same code, and all i did was change @A5 to a Decimal(14,2) (fourteen characters max, two to the right of the decimal) , and the others to have a precision of 14,4instead of a floats:

DECLARE @A1 Decimal (14,4), @A2 Decimal (14,4), @A3 Decimal (14,4), @A4 Decimal (14,4), @A5 Decimal (14,2)

SET @A1 = 150.85

SET @A2 = 200

SET @A3 = 0

SET @A4 = 100

SELECT @A5= ROUND( ((@A1*@A4) / (@A2-@A3) ), 2)

SELECT @A5

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 2

Hi Lowell,

Thanks for quick reply and it is working fine for decimal.

What about for float..?

Thanks.

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Lowell SSC Guru Points: 323507 More actions · Answer 3

ok i think i see the reason, but not the solution.

float, without the size defined , is defaulted to a float(53), which is an 8 byte double precision.

if i change the variables to anything from float(25) to float(53), i get the wrong answer (75.42)

if i use flat(24) or less, i get the correct answer 🙂

DECLARE @A1 float(24), @A2 float(24), @A3 float(24), @A4 float(24), @A5 float(24)