- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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
How to use actual row count (COUNT(*)) in WHERE clause without writing the same query as subquery in MySql?
Achieve this with the help of where clause.
The syntax is as follows
SELECT yourColumnName1,yourColumnName2,...N FROM yourTableName WHERE ( SELECT COUNT(*) FROM yourTableName )=2;
To understand the concept, let us create a table. The query to create a table is as follows
mysql> create table CountWithSubqueryDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > EmployeeName varchar(20) - > ); Query OK, 0 rows affected (2.09 sec)
Insert some records in the table using insert command.
The query is as follows
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('John'); Query OK, 1 row affected (0.54 sec) mysql> insert into CountWithSubqueryDemo(EmployeeName) values(NULL); Query OK, 1 row affected (0.36 sec) mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Carol'); Query OK, 1 row affected (0.23 sec) mysql> insert into CountWithSubqueryDemo(EmployeeName) values(''); Query OK, 1 row affected (0.31 sec) mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Bob'); Query OK, 1 row affected (1.24 sec) mysql> insert into CountWithSubqueryDemo(EmployeeName) values(NULL); Query OK, 1 row affected (0.30 sec) mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Sam'); Query OK, 1 row affected (0.18 sec) mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Mike'); Query OK, 1 row affected (0.18 sec) mysql> insert into CountWithSubqueryDemo(EmployeeName) values('James'); Query OK, 1 row affected (0.26 sec)
Display all records from the table using select statement.
The query is as follows
mysql> select *from CountWithSubqueryDemo;
The following is the output
+----+--------------+ | Id | EmployeeName | +----+--------------+ | 1 | John | | 2 | NULL | | 3 | Carol | | 4 | | | 5 | Bob | | 6 | NULL | | 7 | Sam | | 8 | Mike | | 9 | James | +----+--------------+ 9 rows in set (0.19 sec)
The following is how you can use actual row count in where clause
mysql> select Id,EmployeeName from CountWithSubqueryDemo - > where - > ( - > select count(*) from CountWithSubqueryDemo - > )=2; Empty set (0.03 sec)
In the above query we are getting empty set because it returns the row if the table have two records only. Therefore, let us delete all records from the table and insert 2 records in the table.
The query is as follows
mysql> truncate table CountWithSubqueryDemo; Query OK, 0 rows affected (1.95 sec)
Insert some records in the table using insert command.
The query is as follows
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('James'); Query OK, 1 row affected (0.15 sec) mysql> insert into CountWithSubqueryDemo(EmployeeName) values(NULL); Query OK, 1 row affected (0.46 sec)
Display all records from the table using select statement.
The query is as follows
mysql> select *from CountWithSubqueryDemo;
The following is the output
+----+--------------+ | Id | EmployeeName | +----+--------------+ | 1 | James | | 2 | NULL | +----+--------------+ 2 rows in set (0.00 sec)
Now the following is the query for row count in where clause
mysql> select Id,EmployeeName from CountWithSubqueryDemo - > where - > ( - > select count(*) from CountWithSubqueryDemo - > )=2;
The following is the output
+----+--------------+ | Id | EmployeeName | +----+--------------+ | 1 | James | | 2 | NULL | +----+--------------+ 2 rows in set (0.00 sec)
- Related Articles
- How can I use MySQL subquery as a table in FROM clause?
- How to display the count from distinct records in the same row with MySQL?
- How to get MySQL query result in same order as given by IN clause?
- How to use COUNT() and IF() in a single MySQL query?
- How to use count with CASE condition in a MySQL query?
- Count the same value of each row in a MySQL column?
- How can we use a MySQL subquery with FROM clause?
- How to get number of rows in a table without using count(*) MySQL query?
- How to use MySQL VIEW with WHERE clause?
- MySQL query to count frequency of students with the same age?
- MySQL GROUP BY with WHERE clause and condition count greater than 1?
- How to use MySQL Date functions with WHERE clause?
- How to use a single MySQL query to count column values ignoring null?
- MySQL query to count where more than three columns values are true?
- Generate the row count (serial number) of records after returning the result in MySQL query?
