4
CREATE TABLE "Test" ("ItemQty" DOUBLE NOT NULL );

Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);



Select Sum(ItemQty)
From Test;

Result: 15.400000000000002

Should be 15.4

0

2 Answers 2

2

The type DOUBLE is a floating point type represented in binary internally. Binary floating point numbers will produce "rounding errors" when converted to base 10. If you want precise numeric calculations with a decimal point, you'll have to use the NUMERIC or DECIMAL type.

To help you understand why floating point types have rounding errors when converting to base 10, please see this article:

https://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples

Also, just for your reference, the NUMERIC or DECIMAL types are going to be orders of magnitude slower at calculations compared to the DOUBLE type.

Also see Data Types in SQLite.

2
  • 2
    SQLite does not have NUMERIC or DECIMAL data types.
    – CL.
    Apr 4, 2014 at 8:18
  • 2
    @efesar Why don't you modify your answer given that SQLite does not support NUMERIC or DECIMAL?
    – misha256
    Jun 16, 2015 at 1:13
-1
select printf('%.2d',Sum(ItemQty))

should be

select printf('%.2f',Sum(ItemQty))
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.