Syntax error Add results from several COUNT queries in MySQL?

Add results from several COUNT queries in MySQL?

To add results from several COUNT queries, you can use the following syntax −

SELECT (SELECT COUNT(*) FROM yourTableName1)+
(SELECT COUNT(*) FROM yourTableName2)+
(SELECT COUNT(*) FROM yourTableName3)+
.
.
.
N AS anyAliasName;

Let us use three tables in the test database −

  • users
  • sortingstringdemo
  • userlogintable

Check the table records from the table using a select statement. Let’s take 3 sample tables with records.

The table records for the first table is as follows −

mysql> select *from users;

The following is the output −

+----+----------+---------------------+
| Id | UserName | UserLastseen        |
+----+----------+---------------------+
|  1 | Larry    | 2019-01-15 02:45:00 |
|  2 | Sam      | 2019-01-15 15:01:52 |
|  3 | Mike     | 2019-01-15 02:30:00 |
|  4 | Bob      | 2019-01-15 15:02:00 |
|  5 | David    | 2019-01-15 14:55:00 |
+----+----------+---------------------+
5 rows in set (0.08 sec)

Second table records are as follows −

mysql> select *from sortingstringdemo;

The following is the output −

+--------+
| Amount |
+--------+
| 12.34  |
| 124.50 |
| 9.59   |
| 150.68 |
| 600.54 |
+--------+
5 rows in set (0.06 sec)

Third table records are as follows −

mysql> select *from userlogintable;

The following is the output −

+----+--------+---------------------+
| Id | UserId | UserLoginDateTime   |
+----+--------+---------------------+
|  1 |      2 | 2019-01-27 13:47:20 |
|  3 |      2 | 2019-01-26 11:30:30 |
|  4 |      1 | 2015-03-11 15:23:55 |
+----+--------+---------------------+
3 rows in set (0.18 sec)

Apply the syntax discussed above to add results from several COUNT queries.

In the below query, I have applied the above three table names −

mysql> select (select count(*) from users)
   -> +(select count(*) from sortingstringdemo)
   -> +(select count(*) from userlogintable) as TotalRecords;

The following is the output displaying the count of records from all the three tables −

+--------------+
| TotalRecords |
+--------------+
|           13 |
+--------------+
1 row in set (0.00 sec)
Updated on: 2019-07-30T22:30:24+05:30

191 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements