How can I sum columns across 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 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?

score 26 · Accepted Answer · answered Sep 15 '11 at 14:08

26

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

answered Sep 15 '11 at 14:08

Joe Stefanelli

132,803
19
237
235

1

If i wanted to do a group by on one of the columns which is common for both tables, how do i do that? – Siva Dec 12 '13 at 20:17

score 3 · Answer 2 · answered Nov 15 '16 at 19:48

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;

score 2 · Answer 3 · answered Aug 30 '18 at 03:57

2

And what about:

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

answered Aug 30 '18 at 03:57

PeTaX

21
1

Yeah, what about that? Can you explain how that query solves the problem? – Nico Haase Aug 30 '18 at 06:00
1

Welcome to Stack Overflow. Your answer may be correct, but an explanation would help other readers. For more info read https://stackoverflow.com/help/how-to-answer – Mikkel Aug 30 '18 at 09:04

score 0 · Answer 4 · answered Sep 15 '11 at 14:09

0

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

answered Sep 15 '11 at 14:09

Dark Falcon

43,592
5
83
98

2

You'd need to use a `UNION ALL` here as a `UNION` would remove duplicate values. – Joe Stefanelli Sep 15 '11 at 14:36

How can I sum columns across multiple tables in MySQL?

4 Answers4

Linked