GROUP BY the number of rows returned by GROUP BY in MySQL?

You can use GROUP_CONCAT() for this. To understand the above concept, let us create a table.

The query to create a table is as follows

mysql> create table groupByDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (1.31 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into groupByDemo(Name) values('John');
Query OK, 1 row affected (0.19 sec)
mysql> insert into groupByDemo(Name) values('Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into groupByDemo(Name) values('Carol');
Query OK, 1 row affected (0.10 sec)
mysql> insert into groupByDemo(Name) values('Bob');
Query OK, 1 row affected (0.24 sec)
mysql> insert into groupByDemo(Name) values('Bob');
Query OK, 1 row affected (0.23 sec)
mysql> insert into groupByDemo(Name) values('Bob');
Query OK, 1 row affected (0.17 sec)
mysql> insert into groupByDemo(Name) values('John');
Query OK, 1 row affected (0.16 sec)
mysql> insert into groupByDemo(Name) values('John');
Query OK, 1 row affected (0.07 sec)
mysql> insert into groupByDemo(Name) values('John');
Query OK, 1 row affected (0.11 sec)
mysql> insert into groupByDemo(Name) values('Sam');
Query OK, 1 row affected (0.17 sec)
mysql> insert into groupByDemo(Name) values('Carol');
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from groupByDemo;

The following is the output

+----+-------+
| Id | Name  |
+----+-------+
| 1  | John  |
| 2  | Carol |
| 3  | Carol |
| 4  | Bob   |
| 5  | Bob   |
| 6  | Bob   |
| 7  | John  |
| 8  | John  |
| 9  | John  |
| 10 | Sam   |
| 11 | Carol |
+----+-------+
11 rows in set (0.00 sec)

Here is the query to group by number of rows

mysql> SELECT Counter, GROUP_CONCAT(Name SEPARATOR ', ') as AllName
   -> FROM (SELECT Name, COUNT(Name) as Counter
      -> FROM groupByDemo
      -> GROUP BY Name) tbl
   -> GROUP BY Counter
   -> ORDER BY Counter DESC;

The following is the output

+---------+------------+
| Counter | AllName    |
+---------+------------+
| 4       | John       | 
| 3       | Carol, Bob |
| 1       | Sam        |
+---------+------------+
3 rows in set (0.00 sec)

George John

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• Listing all rows by group with MySQL GROUP BY?
• Get rows with GROUP BY in MySQL?
• Group MySQL rows in an array by column value?
• HAVING with GROUP BY in MySQL
• MySQL- GROUP and COUNT by date?
• MySQL: update field with Group By?
• Limit the count using GROUP BY in MySQL
• SELECT DISTINCT vs GROUP BY in MySQL?
• How to order or choose rows in MySQL GROUP BY clause?
• MySQL group by for separate id without using GROUP BY to remove duplicate column row?
• Group by Operator in C#
• Group by dates in MongoDB?
• How can we apply filtering criteria at group levels of the result set returned by MySQL?
• MySQL query to GROUP BY multiple columns
• MySQL GROUP BY date when using datetime?