CODEWITHSUNDEEP
sql
Tom
Tom

Reputation: 1291

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.

Upvotes: 1

Views: 443

Answers (3)

Lucky
Lucky

Reputation: 713

This is the simplest way:

sqlite> select * from t1;
1|a|10
2|b|5
sqlite> select * from t2;
1|a|2
2|b|6
sqlite> select x.name,x.quantity - y.quantity from t1 x, t2 y where x.name = y.name;
a|8
b|-1
sqlite>

You probably want to do an INNER JOIN for more robustness though.

Upvotes: 1

forpas
forpas

Reputation: 164064

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Give the values aliases and subtract:

SELECT Inv1.Name1, Inv1.TotalQuantity - inv2.TotalQuantity
FROM (SELECT `Item Name` as Name1, SUM(`Quantity`) AS TotalQuantity
      FROM  `table1` 
      GROUP BY `Item Name`
     ) INV1 JOIN
     (SELECT `Item Name` as Name1, SUM(`Quantity`) AS TotalQuantity
      FROM  `table2` 
      GROUP BY `Item Name`
     ) INV2
     ON inv1.Name1  = inv2.Name1

Upvotes: 2

Related Questions