d1ch0t0my d1ch0t0my - 2 years ago 70
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:

reference quantity
TS00001 235
TS00002 400
TS00003 850

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 Source

You need to UNION the two tables:

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


  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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download