Sqlite sum error

Moderator: Rathinagiri

User avatar
karweru
Posts: 178
Joined: Fri Aug 01, 2008 1:51 pm
DBs Used: DBF,mysql,mariadb,postgresql,sqlite,odbc
Been thanked: 7 times
Contact:

Sqlite sum error

Post by karweru » Mon Apr 02, 2018 6:10 am

Greetings

I have a table with a 'debit', 'credit' and 'balance' columns, all type float. The balance column stores a debit-credit and therefore has a mix of positive and negative entries. The problem I have is sqlite sum is sometimes giving incorrect totals, which can cause one to incur financial loses.

Has anyone got an idea how to resolve this?,
Kind regards,
Gilbert.

User avatar
serge_girard
Posts: 2283
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 544 times
Been thanked: 115 times
Contact:

Post by serge_girard » Mon Apr 02, 2018 6:20 am

Gilbert,

FLOAT Unsisgned maybe you used?

Serge

User avatar
dragancesu
Posts: 657
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 20 times
Been thanked: 131 times

Post by dragancesu » Mon Apr 02, 2018 6:37 am

Has anyone got an idea how to your table looks like?

It would be nice to show the structure of the table and query

User avatar
karweru
Posts: 178
Joined: Fri Aug 01, 2008 1:51 pm
DBs Used: DBF,mysql,mariadb,postgresql,sqlite,odbc
Been thanked: 7 times
Contact:

Post by karweru » Mon Apr 02, 2018 7:28 am

Hi Serge, Dragancesu,

The table:

CREATE TABLE `Vclear` (
`T_TYPE` VARCHAR ( 3 ),
`T_DOC` VARCHAR ( 4 ),
`T_REF` VARCHAR ( 20 ),
`T_DATE` DATE ( 8 ),
`T_STAMP` VARCHAR ( 20 ),
`VC_TYPE` VARCHAR ( 10 ),
`VC_DATE` DATE ( 8 ),
`VC_STAMP` VARCHAR ( 15 ),
`VC_PERIOD` FLOAT ( 2 , 0 ),
`ACCOUNT_CODE` VARCHAR ( 20 ),
`VKEY_CODE` VARCHAR ( 20 ),
`VKEY_INFO` VARCHAR ( 40 ),
`VC_AMOUNT` FLOAT ( 15 , 2 ),
PRIMARY KEY(`T_DOC`,`T_REF`,`T_STAMP`,`VC_STAMP`,`ACCOUNT_CODE`,`VKEY_CODE`)
);

The querry:

SELECT TOTAL(vc_amount) FROM Vclear;

The data is as below,...the expected result is Zero, not 2.32830643653870000
data.png
data.png (25.96 KiB) Viewed 1245 times
Kind regards,
Gilbert.

User avatar
serge_girard
Posts: 2283
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 544 times
Been thanked: 115 times
Contact:

Post by serge_girard » Mon Apr 02, 2018 1:26 pm

Hi Gilbert,

Also a piece the data..!
(Insert statements are OK)

Serge

User avatar
serge_girard
Posts: 2283
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 544 times
Been thanked: 115 times
Contact:

Post by serge_girard » Mon Apr 02, 2018 1:48 pm

Gilbert,

Did you try this:

Code: Select all

SELECT SUM(VC_AMOUNT) FROM Vclear
Instead of TOTAL...

Serge

User avatar
dragancesu
Posts: 657
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 20 times
Been thanked: 131 times

Post by dragancesu » Tue Apr 03, 2018 6:23 am

It's SQLite problem with calculation

https://github.com/sparklemotion/sqlite3-ruby/issues/24

User avatar
karweru
Posts: 178
Joined: Fri Aug 01, 2008 1:51 pm
DBs Used: DBF,mysql,mariadb,postgresql,sqlite,odbc
Been thanked: 7 times
Contact:

Post by karweru » Tue Apr 03, 2018 6:54 am

SUM or TOTAL have the same result have the same result. The following statement seems to work,...

SELECT ROUND(TOTAL(VC_AMOUNT),4) FROM Vclear

Not sure it will work for every situation though,...as Dragancesu observes above, it is a big problem in sqlite. Do other sqls have this problem i wonder,...it can be a very big problem when designing applications dealing with money :(
Kind regards,
Gilbert.

User avatar
serge_girard
Posts: 2283
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 544 times
Been thanked: 115 times
Contact:

Post by serge_girard » Tue Apr 03, 2018 8:29 am

Hi Gilbert,

In MySQL it works properly. Better create a loop to calculate the sum !

Serge

User avatar
mustafa
Posts: 824
Joined: Fri Mar 20, 2009 11:38 am
DBs Used: DBF
Location: Alicante - Spain
Been thanked: 117 times
Contact:

Post by mustafa » Sun Jun 16, 2019 1:07 pm

Hola amigos:
Siguiendo con los experimentos con SQLite
La suma de cantidades económicas de precios.
No consigo que en los GRID y en el Listado PDF
las cantidades económicas que terminan en "0"
salgan , vean la muestra sale 3,8 cuando tendía
que ser 3,80 ó 4,0 cuando tendría que ser 4,00
el problema de la "," de los Euros no es el problema
porque si no se aplica STRTRAN ( precio , '.',',')
sale lo mismo falta el "0"
También he aplicado --> ROUND( precio, 2 ) , indicando
que son 2 decimales, solo pasa con los "0" ?

Si alguien puede indicar alguna solución, agradecido
Un cordial saludo.
Mustafa

*---------------------------- Google ---------------------------------------*

Hello friends:
Continuing with the experiments with SQLite
The sum of economic quantities of prices.
I do not get that in the GRID and in the PDF List
the economic amounts that end in "0"
come out, see the sample comes out 3.8 when I tended
that would be 3.80 or 4.0 when it would have to be 4.00
the problem of the "," of the Euros is not the problem
because if STRTRAN is not applied ( precio , '.', ',')
the same thing is missing the "0"
I have also applied -> ROUND ( precio, 2), indicating
which are 2 decimals, only happens with the "0"?

If someone can indicate some solution, grateful
A cordial greeting.
Mustafa
Attachments
SQLite_Sum_Grid_Arrays.zip
(33.46 KiB) Downloaded 16 times
screenshot.jpg
screenshot.jpg (209.66 KiB) Viewed 238 times

Post Reply