Wrong math in sqlite3-CodePudding

so i am working with sqlite3 database for my application and the results of simple math calculations are wrong and I am concerned due to this.. so here goes, i have to find the netflow of incoming and outgoing stock and their flow is being recorded in two separate tables purchases and stock outflow respectively.. i group the purchases and used (outflow) material in the query by their product id and add their quantities from both the tables and then subtract the added quantities from the two tables to get the left quantity of that product or netflow of that product but sqlite3 database fails to do even simple math,

for ex - when a product is purchased in two different quantities 8 and 4 respectively the answer must be 12 but it gives 24 and when the same product is used twice in different quantities like 7 and 4 the answer must be 11 but it gives 22..

below is the SQL query i wrote to find the netflow of each product seperately,

                  SELECT p.product_id,
                         SUM(p.quantity) AS total_inflow,
                         SUM(s.quantity) AS total_outflow,
                         SUM(p.quantity) - SUM(s.quantity) AS quantity_left,
                         s.UOM

                  FROM purchases p
                  INNER JOIN stock_out s ON p.product_id = s.product_id

                  GROUP BY p.product_id, s.product_id

CodePudding user response：

When you have more than one row of either in_flow or out_flow or both, the SUM of the other quantity is impacted due to the JOIN operation.

To address the JOIN issue, you can do something like this, as an example. To address the case of in_flow and no out_flow, and we want to show those results as well, we can use an outer join for this (adjustment made).

If there's a chance of out_flow with no corresponding in_flow, we could use a FULL OUTER JOIN. I suspect that may not be necessary in this case.

WITH in_flow AS (
        SELECT product_id, SUM(quantity) AS total_inflow
          FROM purchases
         GROUP BY product_id
     )
   , out_flow AS (
        SELECT product_id, SUM(quantity) AS total_outflow
          FROM stock_out
         GROUP BY product_id
     )
SELECT in_flow.product_id
     , total_inflow, COALESCE(total_outflow, 0) AS total_outflow
     , total_inflow - COALESCE(total_outflow, 0) AS remaining
  FROM      in_flow
  LEFT JOIN out_flow
    ON in_flow.product_id = out_flow.product_id
;

CodePudding user response：

Aggregate separately in the 2 tables and use UNION ALL for the results.
Then aggregate again:

SELECT product_id, 
       SUM(total_inflow) AS total_inflow, 
       SUM(total_outflow) AS total_outflow,
       SUM(total_inflow) - SUM(total_outflow) AS quantity_left
FROM (
  SELECT product_id, SUM(quantity) AS total_inflow, 0 AS total_outflow
  FROM purchases 
  GROUP BY product_id 
  UNION ALL
  SELECT product_id, 0, SUM(quantity) 
  FROM stock_out 
  GROUP BY product_id
) 
GROUP BY product_id;