Riccardo, please do the following query
select typeof(basket) as tp FROM Data WHERE tp<>"integer" as you may know sqlite accepts any value regardless of your desired type, so you possible could have inserted a real value not knowing about it. This query will probably show the rows with such values Max On Wed, Jul 14, 2010 at 2:43 PM, Riccardo Cohen <r...@architectedulogiciel.fr>wrote: > Hello > I've been using sqlite in many projects (thanks for providing it) and > found today someting strange with sum function > > I have a simple database: > > CREATE TABLE data (irisid char(9),postid varchar(20),basket integer); > CREATE INDEX irisididx on data (irisid); > CREATE INDEX postididx on data (postid); > > created by my c#.net program using sqlite ado component from > http://sqlite.phxsoftware.com/ v1.0.65.0 (cannot say which sqlite version) > > This works perfectly until I need to use sum. I use SQLiteExpertPersonal > 3.0.19 to do some selects : > > NO SUM : > -------- > select irisid,basket from data where irisid in ('372030000') and postid > in ('A_02_001_0001') group by irisid > > irisid basket > 372030000 696 > > DO THE SUM OF THIS ITEM : > ------------------------ > select irisid,sum(basket) from data where irisid in ('372030000') and > postid in ('A_02_001_0001') > > irisid sum(basket) > 372030000 695.81315226 > > same problem if multiple rows, with or without group by, with '=' > instead of 'in'... same also with min() and max(), there is no null, and > no float (all ints) in this record (or any other) > > I cannot understand why this approximation. I tried to build a new small > database in sqlite expert and cannot reproduce the problem. > I cannot send you the database it takes 1,5 Gb (15M records). > I found nothing when googling for this problem. > > I would like to know if there is a simple explanation, before trying to > rebuild my huge database with some tests. > thanks a lot for your help. > > -- > Riccardo Cohen > Architecte du Logiciel > http://www.architectedulogiciel.fr > +33 (0)6.09.83.64.49 > Membre du réseau http://www.reflexe-conseil-centre.org > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
