phpJs phpJs - 3 months ago 15
MySQL Question

How can I sum columns across multiple tables in MySQL?

In MySQL I have two tables:

Table MC:
----------------
|TransNo | Qty |
|--------|-----|
| xxx1 | 4 |
| xxx3 | 3 |


and

Table Amex:
----------------
|TransNo | Qty |
|---------|-----|
| xxx1 | 2 |
| xxx5 | 1 |


I need to sum the
Qty
column from table
MC
(eq. 7) and table
Amex
(eq. 3) and have result as Total Qty.

When I do

SELECT (SUM(amex.Qty) + SUM(mc.Qty)) as total_qty from amex, mc


I get the cartesian product (20), but the correct answer I need is 10. How do I need to change this query to get the correct result?

Answer
SELECT SUM(t.Qty) AS total_qty
    FROM (SELECT Qty FROM MC
          UNION ALL
          SELECT Qty FROM Amex) t
Comments