vendredi 16 octobre 2015

SQLite - SUM of float/decimal results in incorrect sum

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.

Aucun commentaire:

Enregistrer un commentaire