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

Did you tried Left join of both queries and a subtraction in outer query ? — Ven
– Ven, Commented Sep 24, 2018 at 7:20

Madhur Bhaiya · Accepted Answer · 2018-09-24 07:20:44Z

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

ScaisEdge · Accepted Answer · 2018-09-24 07:24:55Z

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

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-24 07:40:33Z

use left join between stockin and stockout ,here you need left join because you may have stockin but may not out

left join your 2 quires

select t1.branchId, t1.quantity - coalesce(t2.quantity,0) result from ( select BranchId, coalesce(sum(Quantity),0) Quantity from stockin where productid=1 group by BranchId ) t1 left join ( Select BranchId, coalesce(sum(Quantity),0) Quantity from stockout where productid=1 group by BranchId ) t2 on t1.BranchId = t2.BranchId branchId result 1 4 2 10

http://sqlfiddle.com/#!9/c549d3/6

This will only return rows if both stock in and stock out are available, use left join instead

Strawberry · Accepted Answer · 2018-09-24 07:36:30Z

select branchid, sum(quantity) total from ( select branchid,quantity from stockin union all select branchid, quantity*-1 from stockout ) x group by branchid;

http://sqlfiddle.com/#!9/c549d3/1

Collectives™ on Stack Overflow

Subtract two columns from two tables with Group By

4 Answers 4

Comments

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Related