0

Occasionally, when selecting aggregate data (usually AVG()) from a database, I'll get a repeating decimal such as:

2.7777777777777777

When I apply ROUND(AVG(x), 2) to the value, I sometimes get a result like:

2.7800000000000002

I happen to know that the actual sample has 18 rows with SUM(x) = 50. So this command should be equivalent:

SELECT ROUND(50.0/18, 2)

However, it produces the expected result (2.78). Why does rounding sometimes produce wrong results with aggregate functions?

In order to verify the above result, I wrote a query against a dummy table:

declare @temp table(
  x float
)

insert into @temp values (1.0);
insert into @temp values (2.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (8.0);
insert into @temp values (9.0);

select round(avg(x), 2),
       sum(x),count(*)
from @temp

I'm aware of the gotchas of floating point representation, but this simple case seems not to be subject to those.

Community
  • 1
  • 1
Jon 'links in bio' Ericson
  • 20,880
  • 12
  • 98
  • 148
  • I've experienced this behaviour before but thought it was by-design.. – AStopher Dec 14 '15 at 19:32
  • "this simple case seems not to be subject to [gotchas of floating point representation]." A very fast way to find out is to use one of [IEEE 754 calculators](http://www.h-schmidt.net/FloatConverter/IEEE754.html). Enter the desired value, and check the resultant representation. For 2.78 you get `2.7799999713897705`. – Sergey Kalinichenko Dec 16 '15 at 18:53
  • 1
    It seems like too many zeroes in the value you got. Trying out the calculator that @dasblinkenlight linked, I see the binary value of 2.7799999713897705 is 01000000001100011110101110000101 (as a double). Incrementing to 01000000001100011110101110000110 yields a decimal value of 2.7800002. ?!? – Erick G. Hagstrom Dec 18 '15 at 14:45
  • Does that mean it's impossible to store any values between 2.7799999713897705 and 2.7800002? Surely there are binary representations for the intervals between. In which case, there should be a way to round more accurately. – CactusCake Dec 18 '15 at 20:10

2 Answers2

3

Decimal numbers don't always (or even usually) map 1:1 to an exact representation in floating point.

In your case, the two closest numbers that double precision floating point can represent are;

There exists no double precision number between those two numbers, in this case the database chose the higher value which - as you see - rounds to 2.7800000000000002.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • That's a sensible explanation as far as it goes. However, this seems to be a implementation quirk since other SQL dialects do the expected thing: `sqlite3 t.db 'select round(cast(25.0/9.0 as double), 2);'` => 2.78 – Jon 'links in bio' Ericson Mar 31 '14 at 19:19
2

Your table uses floats. When given floats as parameters, both AVG() and ROUND() return floats. Floats cannot be precisely represented. When you do ROUND(50.0/18, 2) you're giving it NUMERIC(8,6) which it returns as DECIMAL(8,6).

Try declaring your columns to use a more precise type like DECIMAL(18,9). The results should be more predictable.

JC Ford
  • 6,946
  • 3
  • 25
  • 34
  • You are certainly correct that the problem stems from the type of the argument to `ROUND()` (as can be observed in [this test](http://data.stackexchange.com/stackoverflow/query/179699)). However, I can't change the type of the column. I _was_ able to [work around the problem](http://data.stackexchange.com/stackoverflow/query/179706/rounding-bug-fixed) with `round(avg(cast(x as decimal)), 2)` in the query itself. – Jon 'links in bio' Ericson Mar 31 '14 at 17:58