MySQL count(*) from multiple tables?

To achieve this for multiple tables, use the UNION ALL.

The syntax is as follows

select sum(variableName.aliasName)
from
   (
   select count(*) as yourAliasName from yourTableName1
   UNION ALL
   select count(*) as yourAliasName from yourTableName2
   ) yourVariableName;

Let us implement the above syntax. Here, I am using the sample database which has more tables.

The two tables we are using are

• userdemo
• wheredemo

Here is the query to display all records of both the tables. The query is as follows to display records from table ‘userdemo’.

mysql> select *from userdemo;

The following is the output

+--------+----------+------------------+
| UserId | UserName | RegisteredCourse |
+--------+----------+------------------+
| 1      | John     | Java             |
| 2      | Larry    | C                |
| 3      | Carol    | C++              |
| 4      | Mike     | C#               |
+--------+----------+------------------+
4 rows in set (0.08 sec)

The query is as follows to display records from table ‘wheredemo’.

mysql> select *from wheredemo;

The following is the output

+------+---------+
| Id   | Name    |
+------+---------+
| 101  | Maxwell |
| 110  | David   |
| 1000 | Carol   |
| 1100 | Bob     |
| 115  | Sam     |
+------+---------+
5 rows in set (0.20 sec)

Here is the query to implement count(*) from both the above tables

mysql> select sum(tbl.EachTableCount)
   -> from
   -> (
   -> select count(*) as EachTableCount from userdemo
   -> UNION ALL
   -> select count(*) as EachTableCount from wheredemo
   -> )tbl;

The following is the output

+-------------------------+
| sum(tbl.EachTableCount) |
+-------------------------+
| 9                       |
+-------------------------+
1 row in set (0.00 sec)

Arjun Thakur

Updated on: 30-Jul-2019

10K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started