Why does SQlite return incorrect sum?

Question

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

Answer 1

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.

Answer 2

select printf('%.2d',Sum(ItemQty))

should be

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