CODEWITHSUNDEEP
mysqlsqlsumdifferencemultiple-tables
Paul Haro
Paul Haro

Reputation: 11

Compare two tables to get the differences between them

Hello I have two tables:

Table: 1 11_counted_stock

id_counted_stock    part_number Number of Tag   quantity
1                   abcde       2               1000
2                   aaaaa       4               222
3                   eeeee       6               442

Table:2 11_stock_actual_whs_costed

id_stock    material    batch   A.stock
1           abcde       12334   500
2           aaaaa       23543   233
3           xxxxx       234234  299

Result Query Im looking for

Material    Stock   Counted_Stock   Difference 
abcde       500     1000            -500
aaaaa       233     222             11
eeeee       0       442             -442
xxxxx       299     0               299

Thanks a lot!

This is my best attempt but I could only sum :(

SELECT mat, qty, SUM(qty) as TotalQty
 FROM (
   SELECT 11_counted_stock.part_number AS mat,  (11_counted_stock.quantity) AS qty
    FROM 11_counted_stock
    GROUP BY 11_counted_stock.part_number
UNION
  SELECT 11_stock_actual_whs_costed.material, SUM(11_stock_actual_whs_costed.available_stock) AS quantity
    FROM 11_stock_actual_whs_costed
    GROUP BY 11_stock_actual_whs_costed.material
) as t
GROUP BY mat
;

Upvotes: 0

Views: 125

Answers (1)

Akina
Akina

Reputation: 42739

SELECT Material,
       SUM(Stock) AS Stock,
       SUM(Counted_Stock) AS Counted_Stock,
       SUM(Stock - Counted_Stock) AS Difference
FROM (
    SELECT part_number AS Material, 0 AS Stock, quantity AS Counted_Stock
    FROM 11_counted_stock
        UNION ALL
    SELECT material, stock, 0
    FROM 11_stock_actual_whs_costed
    ) total
GROUP BY Material;

Upvotes: 0

Related Questions