How to calculate running pending value in sqlite

Question

Hello everyone I was trying to create running pending value like running total as I am so close but I was not able to do it. It produces some wrong output

Here is the sqlite export code to create tables and data.

BEGIN TRANSACTION;
CREATE TABLE "tab_in" (
   id INTEGER PRIMARY KEY ASC AUTOINCREMENT, 
   "type" TEXT NOT NULL, "grams" INTEGER NOT NULL
);
INSERT INTO `tab_in` VALUES (1,'type1',1000);
INSERT INTO `tab_in` VALUES (2,'type2',2000);
CREATE TABLE "sub_" (
   id INTEGER PRIMARY KEY ASC AUTOINCREMENT, 
   "gram1" REAL, "gram2" REAL, 
   "gram3" REAL, 
   "_date" TEXT, 
   "id_sub" INTEGER, 
   "record_count" INTEGER, 
   FOREIGN KEY(id_sub) REFERENCES sub(id)
);
INSERT INTO `sub_` VALUES (5,10.0,NULL,NULL,'2017-06-01 00:00:00.000',2,2);
INSERT INTO `sub_` VALUES (7,1.45,NULL,NULL,'2017-06-01 00:00:00.000',1,3);
INSERT INTO `sub_` VALUES (8,12.6,NULL,NULL,'2017-06-01 00:00:00.000',1,4);
INSERT INTO `sub_` VALUES (9,NULL,13.3,NULL,'2017-06-02 00:00:00.000',2,5);
INSERT INTO `sub_` VALUES (10,NULL,NULL,20.46,'2017-06-02 00:00:00.000',2,6);
INSERT INTO `sub_` VALUES (11,6.23,NULL,NULL,'2017-06-02 00:00:00.000',2,7);
CREATE TABLE "sub" (
   id INTEGER PRIMARY KEY ASC AUTOINCREMENT, 
   "_date" TEXT NOT NULL, 
   "gram" REAL NOT NULL, 
   "id_tab_in" INTEGER NOT NULL, 
   "record_count" INTEGER, 
   FOREIGN KEY(id_tab_in) REFERENCES tab_in(id)
);
INSERT INTO `sub` VALUES (1,'2017-05-30 00:00:00.000',14.05,1,1);
INSERT INTO `sub` VALUES (2,'2017-05-30 00:00:00.000',50.0,2,2);
COMMIT;

Here is the query that I have been working on but couldn't make it work.

SELECT DISTINCT 
   "sub_"."_date" as "sub_._date",
   "sub_"."gram1" as "sub_.gram1",
   "sub_"."gram2" as "sub_.gram2",
   "sub_"."gram3" as "sub_.gram3",
   ( 
     (SELECT sub.gram FROM sub WHERE sub.id=sub_.id_sub) -  
     (
        SELECT ifnull(TOTAL(s.gram1), 0) +
               ifnull(TOTAL(s.gram2), 0) +
               ifnull(TOTAL(s.gram3), 0) 
        FROM sub_ s 
        WHERE (s.id_sub=sub_.id_sub) 
          AND (s.record_count <= sub_.record_count )
      )
    )    AS 'sub_.pending',
   "sub_".id,
   "sub_"."id_sub" as "sub_.id_sub" 
 FROM "sub_" 
 LEFT OUTER JOIN "sub" ON "sub_"."id_sub"="sub".id 
 WHERE "sub_"."id_sub"=1;

Output of this query is (For sub.id = 1 value of sub.gram is 14.05) basically what I'm doing here, To calculate pending value I am subtracting from sub.gram using total of sub_.gram1, sub_.gram2, sub_.gram3.

sub_._date                 sub_.gram1   sub_.gram2  sub_.gram3  sub_.pending            id  sub_.id_sub
2017-06-01 00:00:00.000     1.45         (null)     (null)     12.600000000000001       7   1
2017-06-01 00:00:00.000     12.6         (null)     (null)     1.7763568394002505e-15   8   1

Desired output for sub_.pending is

sub_.pending
12.6
0

Please help me to make it work.

Answer 1

Okay, I got it now. You query is correct, though you unnecessarily query sub twice.

Your problem is with your data type. You are storing values as REAL. This is an approximate data type. So you think you are storing 12.6, but it gets stored as 12.600000000000001 for instance. This is something we would normally never do in a database. We would always store exact values. SQLite, however, is very poor considering this, as it doesn't even provide an exact type for decimal numbers.

So in SQLite your best bet seems to be to round. Say you know that your values are only valid up to two decimal places you'd ROUND(value,2):

UPDATE: The rounded values would probably be REALs again :-( Finally your results are correct, only imprecise. So format your output with two decimal places using printf:

select 
  sub_.*,
  printf('%.2f',
    sub.gram -
    (
      select 
        coalesce(sum(s.gram1), 0) +
        coalesce(sum(s.gram2), 0) +
        coalesce(sum(s.gram3), 0)
      from sub_ s
      where s.id_sub = sub_.id_sub
        and s.record_count <= sub_.record_count
    )
  ) as pending
from sub_
join sub on sub.id = sub_.id_sub
order by sub_.id_sub, sub_.record_count;