[SOLVED] SQL SUM Function Problem on Reports

General discussion on all other topics under the sun.

Moderator: Renato

Post Reply
kemaley
New MMEX User
Posts: 7
Joined: Sat Apr 11, 2020 12:36 pm
Are you a spam bot?: No

[SOLVED] SQL SUM Function Problem on Reports

Post by kemaley »

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.


Money Manager Bug 3.png
Money Manager Bug 3.png (104.29 KiB) Viewed 937 times
Money Manager Bug 2.png
Money Manager Bug 2.png (97.66 KiB) Viewed 937 times
Money Manager Bug 1_LI.jpg
Money Manager Bug 1_LI.jpg (51.29 KiB) Viewed 937 times

siowena
MVP MMEX User
Posts: 113
Joined: Tue Jul 17, 2018 8:04 am
Are you a spam bot?: No

Re: SQL SUM Function Problem on Reports

Post by siowena »

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)

Nikolay
MMEX Developer
Posts: 1377
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

Post by Nikolay »

Round(sum(amount),2) as total

kemaley
New MMEX User
Posts: 7
Joined: Sat Apr 11, 2020 12:36 pm
Are you a spam bot?: No

Re: SQL SUM Function Problem on Reports

Post by kemaley »

Thank you.

Post Reply