How to subtract inventory and sale using mysql subquery?

Question

Trying to learn more on sub-query. I am looking for a way to subtract and compare two tables.

1. Inventory
2. Sales

My data records are as follows:

Inventory:

mysql> select store_id, product_id, sum(quantity) as inventory from inventories where store_id = 1 group by product_id;
+----------+------------+-----------+
| store_id | product_id | inventory |
+----------+------------+-----------+
|        1 |          8 |        24 |
|        1 |         10 |         4 |
|        1 |         14 |        24 |
+----------+------------+-----------+
3 rows in set (0.00 sec)

Sales

mysql> select store_id, product_id, sum(quantity) as sales from sales where store_id = 1 group by product_id;
+----------+------------+-------+
| store_id | product_id | sales |
+----------+------------+-------+
|        1 |          8 |    12 |
|        1 |         14 |     2 |
|        1 |          8 |     1 |
+----------+------------+-------+
2 rows in set (0.00 sec)

What is the proper sub-query to have the following result?

+----------+------------+-----------+-------+-----------+
| store_id | product_id | inventory | sales | remaining |
+----------+------------+-----------+-------+-----------+
|        1 |          8 |        24 |    12 |        12 |
|        1 |         14 |        24 |     2 |        22 |
|        1 |          8 |        12 |     1 |        11 |
+----------+------------+-----------+-------+-----------+

Answer 1

You can use results from your queries and join them to calculate the remaining quantity for each product

SELECT 
a.store_id,
a.product_id,
a.inventory,
b.sales,
a.inventory - b.sales AS remaining
FROM (
SELECT store_id, product_id, COALESCE(SUM(quantity),0) AS inventory 
FROM inventories WHERE store_id = 1 
GROUP BY product_id) a
LEFT JOIN (
SELECT store_id, product_id, COALESCE(SUM(quantity),0) AS sales
 FROM sales WHERE store_id = 1 
 GROUP BY product_id ) b USING(store_id, product_id)

Answer 2

To achieve the desired output you need to calculate running totals of product sales. To get meaningful data, the data in sales table must be ordered chronologically. So you need at least one more field to sort data - it doesn't matter if it's a timestamp, or id field. Let's assume there is an id field in sales table. This is a query to get what you described:

SELECT 
    sales.id,
    sales.store_id,
    sales.product_id,
    inventories.quantity-IFNULL(SUM(sales_2.quantity), 0) as inventory,
    sales.quantity as sales,
    inventories.quantity-IFNULL(SUM(sales_2.quantity), 0) - sales.quantity as remaining
FROM
    sales
        INNER JOIN
    inventories ON inventories.store_id = sales.store_id
        AND inventories.product_id = sales.product_id
        LEFT JOIN
    sales AS sales_2 ON sales_2.store_id = sales.store_id
        AND sales_2.product_id = sales.product_id
        AND sales_2.id < sales.id
GROUP BY sales.id , sales.store_id , sales.product_id
ORDER BY sales.id

The second instance of sales table called sales_2 is used to calculate the sum of earlier sales (sales_2.id<sales.id)

You can exclude sales.id from the select clause, but you need to keep it in group by and order by.