How can I sum columns in multiple tables in MySQL?

Question

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 summarize the Qty column from the MC table (equation 7) and the Amex table (equation 3) and get the 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 I need the correct answer. 10. How do I modify this query to get the correct result?

Answer 1

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

Answer 2

If you want to avoid using Union or Union ALL (perhaps for reasons of efficiency), then the following works:

 SELECT (1.Qty+2.Qty) AS total_qty FROM (SELECT SUM(Qty) Qty FROM MC) 1, (SELECT SUM(Qty) Qty FROM Amex) 2; 

Here is an example if you want to expand it to include the Group By clause. Say we have Cust_ID on MC and Amex to identify the customer who placed each order, and we want to know the amounts for each customer. Then the code will look like this:

 SELECT COALESCE(1.Cust_ID, 2.Cust_ID) Cust_ID, (1.Qty+2.Qty) AS total_qty FROM (SELECT Cust_ID, SUM(Qty) Qty FROM MC GROUP BY Cust_ID) 1 FULL OUTER JOIN (SELECT Cust_ID, SUM(Qty) Qty FROM Amex GROUP BY Cust_ID) 2 ON 1.Cust_ID = 2.Cust_ID; 

If the Customer table exists in the database, this can be simplified to:

 SELECT c.Cust_ID, (1.Qty+2.Qty) AS total_qty FROM Customer c LEFT JOIN (SELECT Cust_ID, SUM(Qty) Qty FROM MC GROUP BY Cust_ID) 1 ON 1.Cust_ID = c.Cust_ID LEFT JOIN (SELECT Cust_ID, SUM(Qty) Qty FROM Amex GROUP BY Cust_ID) 2 ON 2.Cust_ID = c.Cust_ID; 

Answer 3

 SELECT SUM(Qty) AS total_qty FROM (SELECT Qty FROM amex UNION SELECT Qty FROM mc);