user3408779 user3408779 -3 years ago 64
MySQL Question

I want to sum same column in 4 different tables mysql

I have 4 tables with different columns. But in 4 tables i have two common columns.one Vendor_id and Stock.This Vendor_id can repeat in all the 4 tables.
Now ny requirement is i want to sum all the stocks related to all the vendor ids in all the 4 tables. For example :

Table One

+----------+-------------+
|vendor_id | stoclk |
+----------+-------------+
| 1234 | 75.00 |
+----------+-------------+
| 1234 | 75.00 |
+----------+-------------+
| 789 | 75.00 |
+----------+-------------+


Table Two

+----------+-------------+
|vendor_id | stoclk |
+----------+-------------+
| 1234 | 5.00 |
+----------+-------------+
| 2389 | 15.00 |
+----------+-------------+
| 789 | 15.00 |
+----------+-------------+


My deisred out put will be

+----------+-------------+
|vendor_id | stoclk |
+----------+-------------+
| 1234 | 155.00 |
+----------+-------------+
| 2389 | 15.00 |
+----------+-------------+
| 789 | 90.00 |
+----------+-------------+


For this i used below query.


SELECT vendor_id,SUM(stock) FROM (
SELECT vendor_id, stock FROM hs_tab1
UNION ALL
SELECT vendor_id, stock FROM hs_tab2
UNION ALL
)a


But i am getting only one vendor id and total number or stocks like this


+----------+-------------+
|vendor_id | stoclk |
+----------+-------------+
| 1234 | 260.00 |
+----------+-------------+


Please tell me some one where i am doing wrong?

Answer Source

Try this:-

SELECT vendor_id,SUM(stock) 
FROM ( SELECT vendor_id, stock FROM hs_tab1
       UNION ALL
       SELECT vendor_id, stock FROM hs_tab2
     )a
GROUP BY vendor_id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download