Subtract two columns from two tables with Group By

Question

I have 4 tables.

CREATE TABLE Branch(
  ID INT,
  Name VARCHAR(50)
);

INSERT INTO Branch VALUES 
(1,'A'), (2,'B');

CREATE TABLE Product(
  ID INT,
  Name VARCHAR(50)
);

INSERT INTO Product VALUES 
(1,'X'), (2,'Y');

CREATE TABLE StockIn(
  ID INT,
  ProductId INT,
  Quantity INT,
  BranchId INT
);

INSERT INTO StockIn VALUES 
(1,1,10,1),
(2,1,20,1),
(3,1,50,2),
(4,1,10,2);

CREATE TABLE StockOut(
  ID INT,
  ProductId INT,
  Quantity INT,
  BranchId INT
);

INSERT INTO StockOut VALUES 
(1,1,5,1),
(2,1,21,1),
(3,1,45,2),
(4,1,5,2);

Now i want to calculate stock from these (StockIn-StockOut).

by using below query i got stockin and stockout from stock tables by grouping thier branches.

StockIn

select BranchId, ifnull(sum(Quantity),0) Quantity from stockin where productid=1 group by BranchId;

StockOut

select BranchId, ifnull(sum(Quantity),0) Quantity from stockout where productid=1 group by BranchId;

and i want to show the result like this

Answer 1

• For each individual Select query results, get an additional field, namely, factor. Its value will be +1 for stock in, and -1 for stockout.
• Combine the results of individual select queries using Union All, and utilize the resultset as a Derived Table.
• Now, simply do a Sum again, multiplying with the factor, on a grouping of BranchId.

Try the following query:

SELECT derived_t.BranchId, 
       SUM(derived_t.factor * derived_t.quantity) AS Quantity 
FROM 
(
 select BranchId, 
        ifnull(sum(Quantity),0) as quantity, 
        1 as factor 
 from stockin 
 where productid=1 
 group by BranchId

 UNION ALL 

 select BranchId, 
        ifnull(sum(Quantity),0) Quantity, 
        -1 as factor
 from stockout 
 where productid=1 
 group by BranchId
) AS derived_t 

GROUP BY derived_t.BranchId

Answer 2

You could use a join between the two query

select a.branchId, a.quantity - ifnull(b.quantity,0) result
from (
    select BranchId, ifnull(sum(Quantity),0) Quantity 
    from stockin 
    where productid=1 
    group by BranchId
) a left join (
   Select BranchId, ifnull(sum(Quantity),0) Quantity 
   from stockout
   where productid=1 
   group by BranchId
) b on a.BranchId = b.BranchId