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 ...

Find difference in quantity column of two tables

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

Advertisement

Answer