CODEWITHSUNDEEP
snowflake-cloud-data-platform
codeBarer
codeBarer

Reputation: 2378

How do you fix rounding error in snowflake when doing calculation?

I'm getting rounding error in snowflake when trying to perform the following calculation.

select ROUND(84186926/91.0)*91.0

The answer I'm getting is 84186921. However, the correct answer is 84186926.

Is there a way to fix this rounding error?

Thanks in advance.

Upvotes: 0

Views: 2536

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

Firstly welcome to floating point numbers. If you 84186926 / 91.0 in Windows Calculator you get 925131.0549450549 which if you * 91 gives you the answer you expect.

Snowflake on the over hand defaults to 6 decimal places so 84186926 / 91.0 gives you 925131.054945 You are then using the ROUND() function which defaults to zero decimal places, so you are getting 925131 Then you multiply by 91.0, giving 84186921

So even if you were to move the closing brackets to be ROUND(4186926/91.0*91.0) you would get 4186926, because now you are ROUNDING 84186925.9.. but really you should never do divisions first as you lose precision, 4186926*91.0/91.0 gives 4186926.000000 as lose of precision (division) is done last.

The other option is to use a format that has the fixed precision you need like number(30,15) thus 84186926::number(30,15)/91.0::number(30,15) thus giving 925131.054945054945055 but really you should swap the order of the operations.

Upvotes: 1

Related Questions