1

I'm trying to get the sum of a column that contains decimal numbers. The result is really strange thought.

I tried this with both float and decimal data types.

Following columns have following datatype:

"euro_out" float
"makes" decimal(10,9)

Some queries and the results:

1.) Expected result when the value is an integer

sqlite> update bookingviews set makes = 1.0;
sqlite> select total(makes) from bookingviews;
==> 1728212.0

2.) unexpected values when the values have decimal values:

sqlite> update bookingviews set makes = 0.1;
sqlite> update bookingviews set euro_out = 0.1;
sqlite> select total(makes) from bookingviews;
==> 172821.200005572
sqlite> select total(euro_out) from bookingviews;
==> 172821.200005572

The correct result should be 172821.2 since there are 1728212 rows with a value of 0.1.

Why the additional 0.000005572? How can I correct this behavior?

Thank you.

Florian
  • 373
  • 5
  • 18

2 Answers2

1

I just stumbled across the same problem. Here is a more complete example to reproduce:

create table test (value decimal(16, 6) not null);
insert into test values (13.709465), (8.606133), (3.961969), (3.5028), (2.886203), (1.298791), (0.695313), (0.629717), (0.380453), (0.28862), (0.275501), (0.262382), (0.236142), (0.170548), (0.170547), (0.157429), (0.144309), (0.13119), (0.065595), (0.065595), (0.065595), (0.065595), (0.065595), (0.065595), (0.052476), (0.052476), (0.052476), (0.039357), (0.039357), (0.039357), (0.026238), (0.026238), (0.026238), (0.026238), (0.026238), (0.026238), (0.026238), (0.026238), (0.013119), (0.013119), (0.013119), (0.013119), (0.013119), (0.013119), (0.013119), (0.013119), (0.013119), (0.013119), (0.013119), (0.013119), (0.013119), (0.013119);
select sum(value) from test;

It outputs:

38.5701510000001

This can be easily recognized as wrong, because summing numbers with not more than six digits after the decimal cannot have more than six digits after the decimal in the result.

Reading the documentation reveals the reason:

If any input to sum() is neither an integer or a NULL then sum() returns a floating point value which might be an approximation to the true sum.

Of course using decimal as a type is supposed to circumvent such issues, however sum is so unkind to use IEEE-Floats anyway.

A possible workaround is to use integers instead. You need to first convert your decimal to an integer, then sum, then convert back. In my example I have a decimal(16, 6) so I need to multiply with 1e6 to get a clean integer:

sqlite> select sum(cast(value * 1e6 as integer)) / 1e6 from test;
38.570151

You have a decimal(10,9) so you need to use 1e9 instead.

Beware that SQLite uses a maximum of 8 bytes to represent an integer, so the maximum value is 2^64, which is about 1.8e19. In your case this means that the maximum number in your database you can work with is ~1.8e10.

yankee
  • 38,872
  • 15
  • 103
  • 162
0

Floating point arithmetic is only accurate to about 15 significant figures for a 64 bit type, or 7 significant figures for a 32 bit type.

As such, they are not designed to be used to represent money.

A simple option is to use a 64 bit integer, and report in "cents"; perhaps cosmetically presenting in "dollars" if appropriate.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483