Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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: 30-Jul-2019
116 Views
Advertisements