CODEWITHSUNDEEP
sqlitefloating-point
ilitirit
ilitirit

Reputation: 16352

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://sqlfiddle.com/#!5/cccd8/3

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

Upvotes: 1

Views: 706

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95562

SQLite's REAL isn't suitable for currency. SQlite doesn't support SQL decimal or SQL numeric data types, so your best option is to use integer, and store values as cents.

CREATE TABLE stock (
  id INTEGER,
  -- Store price and commission as integers, implying that price is in cents,
  -- ($3.20 is stored as 320) and commission is a two-digit percentage (0.57%
  -- is stored as 57).  This is how scaled integers work in general.
  price integer,
  commission integer,
  PRIMARY KEY(id)
);

CREATE TABLE sales (
  id INTEGER,
  stockid INTEGER,
  type INTEGER,
  quantity  INTEGER,
  PRIMARY KEY(id)
);

insert into stock values (1, 320, 57);
insert into sales values (1, 1, 4, 10);
insert into sales values (2, 1, 5, 4);
insert into sales values (3, 1, 5, 6);

This query, from your SQLfiddle, correctly returns 0. 

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;

id          Sales
----------  ----------
1           0

Casting to a more appropriate data type (not to REAL) will hide some problems--maybe even most problems or even all of them in a particular application. But casting won't solve them, because stored values are liable to be different than the values you really want.

Upvotes: 2

Simon Byrne
Simon Byrne

Reputation: 7864

Mike Sherrill is correct in that you probably should use integers. But for a quick-and-dirty fix, you can wrap the Sum call in a Round(__,2) to round to the nearest cent.

Upvotes: 1

Related Questions