I *think* this is due to you creating an integer when you first create the entries
Try changing from INSERT INTO fmtemp VALUES (1, 0); to INSERT INTO fmtemp VALUES (1, 0.0); Just did macpro:js rwillett$ sqlite3 SQLite version 3.8.10.2 2015-05-20 18:17:19 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.0); sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT bal FROM fmtemp; Error: no such column: bal sqlite> SELECT balance FROM fmtemp; 123.45 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 1234.5 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 1357.95 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 4567.65 sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> UPDATE fmtemp SET balance = balance + 123.45; sqlite> SELECT balance FROM fmtemp; 10246.35 sqlite> and it seems to work for me. Rob On 11 Dec 2015, at 14:21, Frank Millman wrote: > Hi all > > 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 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT balance FROM fmtemp; > 246.9 > > I repeat this a number of times, and it runs fine, until this happens > - > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 5802.15 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 5925.59999999999 > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 6049.04999999999 > > Can anyone explain what is going on, and is there a way to avoid it? > > Thanks > > Frank Millman > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
