Android Question Sqlite sum does not return the correct number

Alberto Michelis

Well-Known Member
Licensed User
Longtime User
Hi,
I have a problem with sqlite sum() it seams it is rounding or somethig
I realize some sum where bad in my app so I test it with Sqlite Expert personal...

This is the query for the rows I need to sum:

B4X:
Select  p.[especie],p.moneda,p.cantidad,c.ultimo*(case when c.sector='Bonos' then .01 else 1 end),m.ultimo,(p.cantidad*c.ultimo*(case when c.sector='Bonos' then .01 else 1 end)*m.ultimo+p.pfintereses)
       FROM posicion p
       left outer join cotiza c on c.especie=p.especie And c.moneda=p.moneda      
       left outer join cotiza m on m.especie=p.moneda||'_'

You can see the rows in the image 2, it sums 1046777.99

NOw this is the query for the sum:

B4X:
Select  sum(p.cantidad*c.ultimo*(case when c.sector='Bonos' then .01 else 1 end)*m.ultimo) as tot
       FROM posicion p
       left outer join cotiza c on c.especie=p.especie And c.moneda=p.moneda      
       left outer join cotiza m on m.especie=p.moneda||'_'

You can see the result in image 1 = 1045440

I cant found why they are different!

Attaches goes the db

Any idea?
Thanks
 

Attachments

  • sqlitesum2.png
    sqlitesum2.png
    34.2 KB · Views: 242
  • sqlitesum1.png
    sqlitesum1.png
    19.4 KB · Views: 175
  • portitDB.zip
    30.3 KB · Views: 176

cimperia

Active Member
Licensed User
Longtime User
I suppose you have by now solved the issue: the +p.pfintereses in your first query does not appear in the second :)
 
Upvote 0
Top