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
MySQL GROUP BY with WHERE clause and condition count greater than 1?
To understand the group by with where clause, let us create a table. The query to create a table is as follows −
mysql> create table GroupByWithWhereClause -> ( -> Id int NOT NULL AUTO_INCREMENT, -> IsDeleted tinyint(1), -> MoneyStatus varchar(20), -> UserId int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.57 sec)
Now you can insert some records in the table using insert command. The query is as follows −
mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',101); Query OK, 1 row affected (0.17 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',101); Query OK, 1 row affected (0.19 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',101); Query OK, 1 row affected (0.14 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',102); Query OK, 1 row affected (0.18 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'Undone',102); Query OK, 1 row affected (0.20 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'done',102); Query OK, 1 row affected (0.59 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',103); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',103); Query OK, 1 row affected (0.20 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',103); Query OK, 1 row affected (0.18 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',103); Query OK, 1 row affected (0.10 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',104); Query OK, 1 row affected (0.14 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',104); Query OK, 1 row affected (0.12 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'Undone',105); Query OK, 1 row affected (0.15 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'done',105); Query OK, 1 row affected (0.26 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(1,'done',105); Query OK, 1 row affected (0.12 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',105); Query OK, 1 row affected (0.24 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'Undone',106); Query OK, 1 row affected (0.23 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',106); Query OK, 1 row affected (0.16 sec) mysql> insert into GroupByWithWhereClause(IsDeleted,MoneyStatus,UserId) values(0,'done',106); Query OK, 1 row affected (0.14 sec)
Display all records from the table using select statement.
The query is as follows −
mysql> select *from GroupByWithWhereClause;
The following is the output −
+----+-----------+-------------+--------+ | Id | IsDeleted | MoneyStatus | UserId | +----+-----------+-------------+--------+ | 1 | 0 | Undone | 101 | | 2 | 0 | done | 101 | | 3 | 0 | done | 101 | | 4 | 0 | done | 102 | | 5 | 1 | Undone | 102 | | 6 | 1 | done | 102 | | 7 | 0 | Undone | 103 | | 8 | 0 | done | 103 | | 9 | 0 | done | 103 | | 10 | 0 | done | 103 | | 11 | 0 | done | 104 | | 12 | 0 | Undone | 104 | | 13 | 1 | Undone | 105 | | 14 | 1 | done | 105 | | 15 | 1 | done | 105 | | 16 | 0 | done | 105 | | 17 | 0 | Undone | 106 | | 18 | 0 | done | 106 | | 19 | 0 | done | 106 | +----+-----------+-------------+--------+ 19 rows in set (0.00 sec)
Here is the query to GROUP BY with WHERE clause −
mysql> SELECT * FROM GroupByWithWhereClause -> WHERE IsDeleted= 0 AND MoneyStatus= 'done' -> GROUP BY SUBSTR(UserId,1,3) -> HAVING COUNT(*) > 1 -> ORDER BY Id DESC;
The following is the output −
+----+-----------+-------------+--------+ | Id | IsDeleted | MoneyStatus | UserId | +----+-----------+-------------+--------+ | 18 | 0 | done | 106 | | 8 | 0 | done | 103 | | 2 | 0 | done | 101 | +----+-----------+-------------+--------+ 3 rows in set (0.00 sec)
Updated on: 30-Jul-2019
1K+ Views
Advertisements