**Im trying to get the difference between two tables sums but i keep getting the wrong outcome **

Table1                                           Table2      

| product  | quantity |                          | product  | quantity |
| -------- | -------- |                          | -------- | -------- |
| a        | 7        |                          | a        | 2        |
| b        | 8        |                          | b        | 4        |
| c        | 9        |                          | c        | 1        |
| c        | 7        |                          | c        | 3        |
| a        | 3        |                          | a        | 2        |
| b        | 4        |                          | b        | 3        |

I tried this queury but i got the wrong values:

select table1.product, sum(table1.quantity) - sum(table2.quantity) as difference
from table1
  join table2 on table1.product = table2.product
group by table1.product,table2.product;

Expected Outcome

Table1

CodePudding user response：

UNION ALL the tables (where you take -quantity for table2.) GROUP BY the result.

select product, sum(quantity)
from
(
  select product,  quantity from table1
  union all
  select product, -quantity from table2
) dt
group by product

As requested - some comments:

As a general advice, it's safer to GROUP BY (in subqueries) before joining - since a JOIN can result in multiple rows for a value.

Also, to include a product only found in one of the tables, an outer join would have been needed.