how to do arithmetic calculations on real/decimal/double in sqlite
(1) By ravi (bharad78) on 2021-04-13 03:06:40 [link] [source]
select cast(1.23 as decimal) + cast(2.34 as decimal) + cast(3.45 as decimal) - cast(1.23 as decimal) - cast(2.34 as decimal) - cast(3.45 as decimal)
results in a value of -8.881784197001252e-16
any idea on how should the values be stored so that I get 0 as the answer
(2) By Keith Medcalf (kmedcalf) on 2021-04-13 06:49:03 in reply to 1 [link] [source]
This is just "how computers work". See https://sqlite.org/floatingpoint.html
The result is within 8 ULP of zero. If you want the result rounded to two decimal places, then round the result.
select round(1.23 + 2.34 + 3.45 - 1.23 - 2.34 - 3.45, 2);
Then the result is 0.0 rounded to two decimal places.
(3) By Warren Young (wyoung) on 2021-04-13 07:05:26 in reply to 2 [source]
See https://sqlite.org/floatingpoint.html
(4) By Hardy on 2021-04-13 12:58:42 in reply to 1 [link] [source]
You may use decimal_add() to get the result you want instead of the ‚+‘ operator. The ‚+‘ operator converts everything back to floats, I suppose, as there is no overload of operators available in SQLite. Take care, according to the documentation you have to pass text strings to decimal_xxx() and not numbers!
See https://www.sqlite.org/floatingpoint.html.