MySQL query to calculate sum from 5 tables with a similar column named “UP”?
For this, use UNION ALL along with SUM(). Let us create 5 tables −
mysql> create table DemoTable1977
(
UP int
);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into DemoTable1977 values(10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1977 values(20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1977;
+------+
| UP |
+------+
| 10 |
| 20 |
+------+
2 rows in set (0.00 sec)
mysql> create table DemoTable1978
(
UP int
);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into DemoTable1978 values(30);
Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1978;
+------+
| UP |
+------+
| 30 |
+------+
1 row in set (0.00 sec)
mysql> create table DemoTable1979
(
UP int
);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into DemoTable1979 values(40);
Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1979;
+------+
| UP |
+------+
| 40 |
+------+
1 row in set (0.00 sec)
mysql> create table DemoTable1980
(
UP int
);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into DemoTable1980 values(50);
Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1980;
+------+
| UP |
+------+
| 50 |
+------+
1 row in set (0.00 sec)
mysql> create table DemoTable1981
(
UP int
);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into DemoTable1981 values(60);
Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1981;
+------+
| UP |
+------+
| 60 |
+------+
1 row in set (0.00 sec)
Here is the query to calculate sum from 5 tables with a single column named “UP” −
mysql> select sum(TotalSum)
from (
select sum(UP) as TotalSum
from DemoTable1977
union all
select sum(UP)
from DemoTable1978
union all
select sum(UP)
from DemoTable1979
union all
select sum(UP)
from DemoTable1980
union all
select sum(UP)
from DemoTable1981
) tbl;
This will produce the following output −
+---------------+
| sum(TotalSum) |
+---------------+
| 210 |
+---------------+
1 row in set (0.00 sec)
Published on 31-Dec-2019 12:32:12
