I have typeof(basket)=real in all records ... I just see now that all data are float numbers in the text file that was used for inserting rows, and did not notice this because SqliteExpert show only ints !
I understand that sqlite accepts data that are not exactly in declared format (other usual example is a string that is bigger than the field), but in my case I must admit that it does not help... Thanks a lot for your quick and efficient help. Max Vlasov wrote: > 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 > -- 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
