How can I sum columns across multiple tables in My

问题:

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?

回答1:

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

回答2:

If you wish to avoid using Union or Union ALL (probably for efficiency reasons), 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's an example for if you wish to expand this out to include a Group By condition. Let's say we have a Cust_ID on both MC and Amex to identify the customer which made each order, and we want to know the sums for each customer. The code would then 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 a Customer table exists in the database, then 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;

回答3:

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

回答4:

And what about:

SELECT (SELECT SUM(`Qty`) FROM `MC`) + (SELECT SUM(`Qty`) FROM `Amex`) AS `sumf`;