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)