Sqlite sum error

[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?,

[serge_girard]

Gilbert,

FLOAT Unsisgned maybe you used?

Serge

[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

[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 4457 times

[serge_girard]

Hi Gilbert,

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

Serge

[serge_girard]

Gilbert,

Did you try this:

Code: Select all

SELECT SUM(VC_AMOUNT) FROM Vclear

Instead of TOTAL...

Serge

[dragancesu]

It's SQLite problem with calculation

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

[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

[serge_girard]

Hi Gilbert,

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

Serge

[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