Find difference in quantity column of two tables

Tags:



Suppose I have two tables, with a item name and quantity:

Table 1

Item Name Quantity
a 10
b 15
c 2

Table 2

Item Name Quantity
a 45
b 2

I want to find the difference in quantity of items from both tables e.g. table 2 – table 1

Item Name Quantity
a 35
b -13
c -2

I already have code to get the appropriate data from each table, I’m just struggling to apply the difference:

SELECT ???INV2-INV1??? From

(SELECT `Item Name` as Name1, SUM(`Quantity`) AS TotalQuantity
FROM  `table1` 
GROUP BY `Item Name`) INV1,

(SELECT `Item Name` as Name1, SUM(`Quantity`) AS TotalQuantity
FROM  `table2` 
GROUP BY `Item Name`) INV2;

How can I find the difference between INV1 and INV2? Thanks for any help.

Answer

Use UNION ALL to get all the rows of the 2 tables (with negative quantities for table1) and then aggregate:

SELECT `Item Name`, SUM(Quantity) total_Quantity
FROM (
  SELECT `Item Name`, Quantity FROM table2 
  UNION ALL
  SELECT `Item Name`, -Quantity FROM table1
) t
GROUP BY `Item Name`

See the demo.
Resuts:

Item Name total_Quantity
a 35
b -13
c -2


Source: stackoverflow