Page 1 of 2
Sqlite sum error
Posted: Mon Apr 02, 2018 6:10 am
by karweru
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?,
Re: Sqlite sum error
Posted: Mon Apr 02, 2018 6:20 am
by serge_girard
Gilbert,
FLOAT Unsisgned maybe you used?
Serge
Re: Sqlite sum error
Posted: Mon Apr 02, 2018 6:37 am
by dragancesu
Has anyone got an idea how to your table looks like?
It would be nice to show the structure of the table and query
Re: Sqlite sum error
Posted: Mon Apr 02, 2018 7:28 am
by karweru
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 (25.96 KiB) Viewed 4503 times
Re: Sqlite sum error
Posted: Mon Apr 02, 2018 1:26 pm
by serge_girard
Hi Gilbert,
Also a piece the data..!
(Insert statements are OK)
Serge
Re: Sqlite sum error
Posted: Mon Apr 02, 2018 1:48 pm
by serge_girard
Gilbert,
Did you try this:
Instead of TOTAL...
Serge
Re: Sqlite sum error
Posted: Tue Apr 03, 2018 6:23 am
by dragancesu
Re: Sqlite sum error
Posted: Tue Apr 03, 2018 6:54 am
by karweru
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
Re: Sqlite sum error
Posted: Tue Apr 03, 2018 8:29 am
by serge_girard
Hi Gilbert,
In MySQL it works properly. Better create a loop to calculate the sum !
Serge
Re: Sqlite sum error
Posted: Sun Jun 16, 2019 1:07 pm
by mustafa
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