CODEWITHSUNDEEP
mysqlsql
Arthur
Arthur

Reputation: 1548

Calculate stock from two transaction tables to a third one in MySQL

I have two tables, one for incoming shipments and one for outgoing. There are a lot of stock queries in various groupings and they need to be fast, so I'd like to create a third table summing the above two and run the queries on that. Is this possible using SQL only?

Simplified example:

Table: INCOMING
id  productid  amount_in
------------------------
1   6          100
2   5          300
3   6           50
4   2           10

Table: OUTGOING
id  productid  amount_out
-------------------------
1   5          10
2   5          20
3   6          30

Resulting table should contain the remaining stock:

Table: STOCK
productid  amount
---------------------
2          10
5          270   -- 300-10-20
6          120   -- 100+50-30

Upvotes: 0

Views: 940

Answers (2)

KAD
KAD

Reputation: 11102

I have grouped the two tables using UNION ALL, to preserve similar records, then used an INSERT INTO SELECT statement from the obtained table by inserting the product id and the SUM of the amount, multiply the amount_out by -1 to subtract it in sum. 

INSERT INTO STOCK 
SELECT productid, SUM(amount)
FROM(
        SELECT productid , amount_in AS amount FROM INCOMING

        UNION ALL

        SELECT productid , amount_out * -1 AS amount FROM OUTGOING)
GROUP BY productid

Upvotes: 2

SMA
SMA

Reputation: 37023

Try something like:

SELECT productid, in - out AS amount
FROM
((SELECT productid, SUM(amount_in) AS in
FROM INCOMING
GROUP BY productid) AS a
INNER JOIN
(SELECT productid, SUM(amount_out) AS out
FROM OUTGOING
GROUP BY productid) AS b
ON a.productid = b.productid)

Upvotes: 1

Related Questions