mercredi 22 avril 2015

SQLite floating point issue

This query returns 1.7763568394002505e-15 when it should return 0.00:

SELECT st.id
, Sum(
    CASE sa.Type 
    WHEN 4 THEN sa.quantity * (st.price - st.commission)
    WHEN 5 THEN -sa.quantity * (st.price - st.commission)
    ELSE 0.0 END
) Sales
FROM sales sa
JOIN stock st
  ON sa.stockid = st.id
WHERE st.id = 1
GROUP BY st.id

http://ift.tt/1biOUzH

It's looks like a classic floating point calculation issue, but how can I fix it? I've tried casting the various columns to REAL but it doesn't make a difference.

You can simulate the result using this query:

SELECT 26.3 - 10.52 - 15.78 AS Result

Aucun commentaire:

Enregistrer un commentaire