Sqlite Reveals Floating Point When Sum Is Subtracted From Same Total Amount In Literal

I have a table that have amount column. When add up, the sum looks normal. But when it is subtracted from the same total in literal, there's a floating point. Consider this sample

Solution 1:

SQLite cheats on data types. Unlike other databases, it ignores your declared column type and guesses the type based on what you're inserting. number is not a valid SQL type, but SQLite doesn't care.

Floating point numbers are only an approximation and will accumulate errors if you do math on them. This is a general problem with floating point numbers. SQLite will turn 1083.33 into a floating point number.

What you need is an arbitrary precision numeric data type like decimal or numeric. Unfortunately SQLite does not have these.

You can mitigate the problem by rounding.

select
  sum(amount),
  round(5947.6-sum(amount), 2) difference
from a;

But this is not 100% accurate as errors will still accumulate, you'll just lop them off at the end. If you want accuracy, store everything in integers and shift their values over two decimal places.

sqlite>insertinto a values(1006118,108333);
sqlite>insertinto a values(1006123,61429);
sqlite>insertinto a values(1006124,78333);
sqlite>insertinto a values(1006096,78333);
sqlite>insertinto a values(1006104,83333);
sqlite>insertinto a values(1006115,53333);
sqlite>insertinto a values(1006116,63333);
sqlite>insertinto a values(1006092,68333);
sqlite>selectsum(amount), 594760-sum(amount) difference from a;
sum(amount)  difference
-----------  ----------5947600

Solution 2:

Digital computer floating point arithmetic is inexact. Some decimal numbers cannot be represented exactly as floating point numbers, so when you ask for the sum of some inexactly representable numbers the result isn't internally represented as an exact decimal number, and so when you subtract what you think is the exact decimal result you don't end up with zero.

Normally such inexactness can be hidden with careful rounding:

select round(sum(amount),2), 5947.6-round(sum(amount),2) difference from a;

See What Every Computer Scientist Should Know About Floating-Point Arithmetic