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?

Joe Stefanelli · Accepted Answer · 2011-09-15 14:08:57Z

26

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

answered Sep 15, 2011 at 14:08

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siva Over a year ago

If i wanted to do a group by on one of the columns which is common for both tables, how do i do that?

Torin Pena · Accepted Answer · 2016-11-15 19:48:42Z

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;

PeTaX · Accepted Answer · 2018-08-30 03:57:55Z

2

And what about:

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

answered Aug 30, 2018 at 3:57

PeTaX

211 bronze badge

2 Comments

Nico Haase Over a year ago

Yeah, what about that? Can you explain how that query solves the problem?

Mikkel Over a year ago

Welcome to Stack Overflow. Your answer may be correct, but an explanation would help other readers. For more info read stackoverflow.com/help/how-to-answer

Dark Falcon · Accepted Answer · 2011-09-15 14:09:44Z

0

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

answered Sep 15, 2011 at 14:09

Dark Falcon

44.2k6 gold badges91 silver badges104 bronze badges

1 Comment

Joe Stefanelli Over a year ago

You'd need to use a UNION ALL here as a UNION would remove duplicate values.

Collectives™ on Stack Overflow

How can I sum columns across multiple tables in MySQL?

4 Answers 4

1 Comment

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related