d1ch0t0my - 8 months ago 17

SQL Question

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.