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 [link] [source]

See https://sqlite.org/floatingpoint.html

Also...

(4) By Hardy on 2021-04-13 12:58:42 in reply to 1 [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.