0
SELECT ROUND(4.524965325714286, 2);

I'd have thought that should be 4.53, because

  • 6 rounds 8 to 9
  • 9 rounds 2 to 3
  • 3 leaves 4 as 4
  • ..
  • so. it ends up lastly doing 4.525 rounds to 4.53

It looks like it's doing

  • I want 4.524965325714286 as 2 decimal places
  • Q: What's the third decimal? A: 4
  • 4 doesn't round up
  • Return 4.52

Is that a correct representation of what happens?

I'm using MySQL version 5.7.20

closed as off-topic by Joe Obbish, mustaccio, Philᵀᴹ, Mr.Brownstone, RDFozz Oct 31 '17 at 17:55

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Joe Obbish, mustaccio, Philᵀᴹ, Mr.Brownstone, RDFozz
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Yes, this is correct. It's also the definition in the documentation, and also how mathematicians do it. – Colin 't Hart Oct 31 '17 at 22:15
2

Round's job is to

  1. Lose precision
  2. Get you as close as it can to the proper value

You have an algorithm, but how close does it get you to the proper value?

SELECT abs(4.524965325714286-4.52) AS "452",
  abs(4.524965325714286-4.53) AS "453";
        452        |        453        
-------------------+-------------------
 0.004965325714286 | 0.005034674285714
(1 row)

As you can see, you're actually further away and you've done more work. Because of that, that's why rounding doesn't work as you had intended.

You could make an iterative rounding function to do what you want but that's a different question.

3

When it is looking at rounding it only looks at the next decimal place to see which direction to round. So in your case it is seeing 4.524 which does not round up to 4.53. I have never heard of a system that rounds up all decimal values until it gets to your limit.

Think of it this way if 2 decimal points is what is important (think a lot of currencies) would you really want the 4th or later decimal value to impact what is calculated for a payment amount?

Not the answer you're looking for? Browse other questions tagged or ask your own question.