Sqlite sum error
Moderator: Rathinagiri
- karweru
- Posts: 220
- Joined: Fri Aug 01, 2008 1:51 pm
- DBs Used: DBF,mysql,mariadb,postgresql,sqlite,odbc
- Contact:
Sqlite sum error
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?,
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.
Gilbert.
- serge_girard
- Posts: 3178
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: Sqlite sum error
Gilbert,
FLOAT Unsisgned maybe you used?
Serge
FLOAT Unsisgned maybe you used?
Serge
There's nothing you can do that can't be done...
- dragancesu
- Posts: 926
- Joined: Mon Jun 24, 2013 11:53 am
- DBs Used: DBF, MySQL, Oracle
- Location: Subotica, Serbia
Re: Sqlite sum error
Has anyone got an idea how to your table looks like?
It would be nice to show the structure of the table and query
It would be nice to show the structure of the table and query
- karweru
- Posts: 220
- Joined: Fri Aug 01, 2008 1:51 pm
- DBs Used: DBF,mysql,mariadb,postgresql,sqlite,odbc
- Contact:
Re: Sqlite sum error
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
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
Kind regards,
Gilbert.
Gilbert.
- serge_girard
- Posts: 3178
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: Sqlite sum error
Hi Gilbert,
Also a piece the data..!
(Insert statements are OK)
Serge
Also a piece the data..!
(Insert statements are OK)
Serge
There's nothing you can do that can't be done...
- serge_girard
- Posts: 3178
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: Sqlite sum error
Gilbert,
Did you try this:
Instead of TOTAL...
Serge
Did you try this:
Code: Select all
SELECT SUM(VC_AMOUNT) FROM Vclear
Serge
There's nothing you can do that can't be done...
- dragancesu
- Posts: 926
- Joined: Mon Jun 24, 2013 11:53 am
- DBs Used: DBF, MySQL, Oracle
- Location: Subotica, Serbia
- karweru
- Posts: 220
- Joined: Fri Aug 01, 2008 1:51 pm
- DBs Used: DBF,mysql,mariadb,postgresql,sqlite,odbc
- Contact:
Re: Sqlite sum error
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
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.
Gilbert.
- serge_girard
- Posts: 3178
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: Sqlite sum error
Hi Gilbert,
In MySQL it works properly. Better create a loop to calculate the sum !
Serge
In MySQL it works properly. Better create a loop to calculate the sum !
Serge
There's nothing you can do that can't be done...
- mustafa
- Posts: 1162
- Joined: Fri Mar 20, 2009 11:38 am
- DBs Used: DBF
- Location: Alicante - Spain
- Contact:
Re: Sqlite sum error
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
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 209 times
-
- screenshot.jpg (209.66 KiB) Viewed 3553 times