Frank, The problem you are having is due to SQLite not following the SQL standard regarding non-integral numeric types.
The SQL standard specifies that the DECIMAL type is exact numeric and able to represent decimal numbers exactly. However, when you ask SQLite for a DECIMAL column, that is not what it will give you; instead, it will silently "succeed" but give you an inexact numeric type instead, a floating point number, as if you had said FLOAT/etc instead of DECIMAL. So the problem you are having is due to the actual numbers in the database not being what you told it to store, but just an approximation. Per another suggestion, the best workaround is to use an INTEGER type instead, and store an even multiple of whatever your smallest currency unit size is, eg cents rather than dollars. -- Darren Duncan On 2015-12-11 6:21 AM, Frank Millman wrote: > I am having a problem accumulating decimal values. > > I am actually using Python, but I can reproduce it in the sqlite3 interactive > terminal. > > SQLite version 3.8.6 2014-08-15 11:46:33 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); > sqlite> INSERT INTO fmtemp VALUES (1, 0); > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 123.45 <snip>
