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)