3
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

  • 1
    I'm sure SQLite thinks it's correct. Have you tried using a different data type? – Andriy M Apr 3 '14 at 19:16
  • 2
    @AndriyM is right. This is the correct answer, 15.4 is wrong. No, sorry, 15.4 is correct as well. – ypercubeᵀᴹ Apr 3 '14 at 20:04
3

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.

|improve this answer|||||
  • Do you know if it would be better performance to just use DECIMAL or to use Round(Sum(ItemQty),2) in my selects? – user2125348 Apr 3 '14 at 20:21
  • I believe rounding would offer better performance, but personally I would run an empirical test to verify that. – efesar Apr 3 '14 at 21:27
  • 2
    SQLite does not have NUMERIC or DECIMAL data types. – CL. Apr 4 '14 at 8:18
  • 2
    @efesar Why don't you modify your answer given that SQLite does not support NUMERIC or DECIMAL? – misha256 Jun 16 '15 at 1:13
-1

select printf('%.2d',Sum(ItemQty)) should do the job
See also the printf() core function

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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