I think there is a problem about sum function on sql report. As you can see in the picture Category of "Borç" has pozitive values, and "Borç Geri Ödemesi" has negative values. Sum of Borç Category is 300 and sum of Borç Geri Ödemesi Category is -300, but amount of them is -7,10542. Payee is same person. How could it be? It is wrong number, it should be 0. Could you help me please if I am wrong. Thank you.
[SOLVED] SQL SUM Function Problem on Reports
Moderator: Renato
Re: SQL SUM Function Problem on Reports
Please note the e-15 in the return of the 1st query.
-7.105427357601e-15
So this is really a difference of -0.000000000000007105427357601
I had this issue with some of my reports. Looking around the web it seems to be an issue with SQLLite in the way it stored the numbers and it's precision of storing the values. e.g. -300 is not stored as 300 negative, but probably the binary equivalent of -299.999999999999928946..
I've had to use the round function e.g. SELECT Payee, ROUND(SUM(amount), 2)
-7.105427357601e-15
So this is really a difference of -0.000000000000007105427357601
I had this issue with some of my reports. Looking around the web it seems to be an issue with SQLLite in the way it stored the numbers and it's precision of storing the values. e.g. -300 is not stored as 300 negative, but probably the binary equivalent of -299.999999999999928946..
I've had to use the round function e.g. SELECT Payee, ROUND(SUM(amount), 2)
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: SQL SUM Function Problem on Reports
Round(sum(amount),2) as total
Re: SQL SUM Function Problem on Reports
Thank you.