2

I run this sql statement on Oracle 11g database

select round(79/3 + 87/3 + 86/6 + 95/6) from dual

and it returns 85

select round(79/3 + 87/3 + 86/6 + 95/6,1) from dual

returns 85.5

select round(85.5) from dual

returns a correct value 86

Does anyone know why the first SQL statement doesn't return a correct value 86 but it rounds it down to 85 ??

mefist
  • 23
  • 1
  • 3
  • 1
    per the docs, it will round to the number of places to right of decimal you specify, default being 0. So Oracle isn't wrong, you just didn't read the docs. So the "correct" value depends on what you tell Oracle to do. – tbone Jul 09 '12 at 17:34

3 Answers3

10

If you do:

select 79/3 + 87/3 + 86/6 + 95/6 from dual;

Oracle will return 85.49999999999999999999999999999999999999, which when rounded to 0 decimal places is indeed 85. It's an artifact of floating point arithmetic.

Xophmeister
  • 8,884
  • 4
  • 44
  • 87
  • Any idea how to make ROUND function to round this correctly? I'm thinking about something like that **select round((79/3 + 87/3 + 86/6 + 95/6)+0.000000001) from dual** BTW I'm only interested in zero precision – mefist Jul 09 '12 at 16:01
  • Round it twice: `select round(round(79/3 + 87/3 + 86/6 + 95/6, 1)) from dual` Although, off the top of my head, this isn't guaranteed to work in general cases. – Xophmeister Jul 09 '12 at 16:07
1

79/3 + 87/3 + 86/6 + 95/6 will return floating point arithmetic which doesn't have the rounding behavior of IEEE 754. See the difference between Number and Float here

To get the correct result back you need to run the statement as:

select round(to_number(79/3 + 87/3 + 86/6 + 95/6)) from dual;
Community
  • 1
  • 1
Java SE
  • 2,073
  • 4
  • 19
  • 25
-1

It looks to be a curious loss of precision.

SELECT 79/3 + 87/3 + 86/6 + 95/6 FROM DUAL

85.49999999999999999999999999999999999999

Obviously 85.4 will then round to 85. As to why Oracle loses the precision in this case, I'm not sure.

Craton
  • 1
  • 1
  • You can work it out with a calculator. 79/3 = 26 1/3 but a calculator will show 26.33333333.. - when you add up the four numbers, you get 85.5; when the calculator does it, it gets 85.499999999.. because it cannot exactly represent a 1/3 fraction in its intermediate calculations. – Jeffrey Kemp Jul 12 '12 at 06:07