MySQL-এ GROUP BY দিয়ে ডISTINCT ভ্যালু ও কাউন্ট পাওয়া

GROUP BY ও COUNT(*) দিয়ে ফ্রিকোয়েন্সি

একটি কলামের প্রতিটি ইউনিক ভ্যালু কতবার আছে তা জানতে GROUP BY এবং COUNT(*) ব্যবহার করুন।

সিনট্যাক্স

SELECT yourColumnName, COUNT(*) AS anyAliasName FROM yourTableName GROUP BY yourColumnName;

উদাহরণ

টেবিল তৈরি

mysql> create table GroupByAndCountDemo (
    -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> ClientName varchar(100)
    -> );
Query OK, 0 rows affected (0.64 sec)

ডেটা ইনসার্ট (বারবার John, Sam)

mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.18 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Sam');
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Sam');
Query OK, 1 row affected (0.16 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.51 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.15 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Sam');
Query OK, 1 row affected (0.08 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Sam');
Query OK, 1 row affected (0.47 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Sam');
Query OK, 1 row affected (0.09 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.18 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('John');
Query OK, 1 row affected (0.10 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('David');
Query OK, 1 row affected (0.12 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Maxwell');
Query OK, 1 row affected (0.17 sec)
mysql> insert into GroupByAndCountDemo(ClientName) values('Maxwell');
Query OK, 1 row affected (0.15 sec)

সকল রেকর্ড (১৬ রো)

mysql> SELECT * FROM GroupByAndCountDemo;

আউটপুট (সংক্ষিপ্ত)

+----------+------------+
| ClientId | ClientName |
+----------+------------+
|        1 | John       |
|        2 | Carol      |
|        3 | Sam        |
|        4 | Sam        |
|        5 | John       |
... (মোট ১৬ রো) ...
+----------+------------+

GROUP BY দিয়ে কাউন্ট

mysql> SELECT ClientName, COUNT(*) AS TotalCount FROM GroupByAndCountDemo GROUP BY ClientName;

আউটপুট

+------------+------------+
| ClientName | TotalCount |
+------------+------------+
| John       |          7 |
| Carol      |          1 |
| Sam        |          5 |
| David      |          1 |
| Maxwell    |          2 |
+------------+------------+

সর্ট করতে: ORDER BY TotalCount DESC যোগ করুন।