d1ch0t0my d1ch0t0my - 7 months ago 14
SQL Question

mySQL sum of two values in 2 different tables

I have 2 identical tables with identical columns in each one - "quantity" and "reference". The values in these columns are set out as follows:

table_1
reference quantity
TS00001 235
TS00002 400
TS00003 850
...

table_2
reference quantity
TS00001 670
TS00002 210
TS00003 150
...


I need to join the tables and output the sum of the quantity field for each matched reference ID e.g:

reference total_quantity
TS00001 905
TS00002 610
TS00003 1000
...


I've been trying LEFT JOIN and other methods but I'm getting nowhere quickly so if anyone could spare the time to steer me on to the right track I'd be very grateful. Thanks.

Answer

You need to UNION the two tables:

SELECT reference, SUM(quantity) AS total_quantity
FROM (
  SELECT reference, quantity
  FROM table_1

  UNION ALL

  SELECT reference, quantity
  FROM table_2) AS t
GROUP BY reference

This way you are guaranteed to get a record for a reference value even if this is contained in only one of the two tables.