MySQL query to count rows in multiple tables

Let us first create a table −

mysql> create table DemoTable1 (FirstName varchar(100));
Query OK, 0 rows affected (0.54 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1 values('Bob');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1 values('James');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1 values('John');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable1 values('David');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1;

This will produce the following output -

+-----------+
| FirstName |
+-----------+
| Bob       |
| James     |
| John      |
| David     |
+-----------+
4 rows in set (0.00 sec)

Here is the query to create second table −

mysql> create table DemoTable2 (Marks int);
Query OK, 0 rows affected (0.65 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2 values(98);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable2 values(89);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable2 values(67);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable2 values(34);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable2 values(32);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable2 values(42);
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable2;

This will produce the following output -

+-------+
| Marks |
+-------+
| 98    |
| 89    |
| 67    |
| 34    |
| 32    |
| 42    |
+-------+
6 rows in set (0.00 sec)

Here is the query to count rows in multiple tables −

mysql> select
   (select count(*) from DemoTable1) AS First_Table_Row,
   (select count(*) from DemoTable2) AS Second_Table_Row;

This will produce the following output -

+-----------------+------------------+
| First_Table_Row | Second_Table_Row |
+-----------------+------------------+
| 4               | 6                |
+-----------------+------------------+
1 row in set (0.00 sec)

AmitDiwan

e

Updated on: 22-Aug-2019

780 Views

Kickstart Your Career

Get certified by completing the course

Get Started