MySQL-এ GROUP BY দিয়ে ফেরত আসা সারির সংখ্যা গ্রুপ করা

এই কাজে GROUP_CONCAT() ব্যবহার করা যায়। ধারণা বুঝতে একটি টেবিল তৈরি করি।

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

INSERT কমান্ড ব্যবহার করে রেকর্ড যোগ করি।

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)

SELECT স্টেটমেন্ট ব্যবহার করে সকল রেকর্ড দেখি।

mysql> select * from groupByDemo;

+----+-------+ 
| 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)

সারির সংখ্যা অনুযায়ী গ্রুপ করার কোয়েরি:

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;

আউটপুট:

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