ดึงค่าที่ไม่ซ้ำและนับจำนวนใน MySQL ด้วย GROUP BY

คำสั่งหลัก

ใช้ GROUP BY ร่วมกับ COUNT(*) เพื่อได้ distinct values พร้อมจำนวน

รูปแบบคำสั่ง

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

ตัวอย่างครบถ้วน

1. สร้างตารางลูกค้า

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

2. เพิ่มข้อมูล (มีชื่อซ้ำมาก)

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)

3. ดูข้อมูลทั้งหมด (16 แถว)

mysql> select * from GroupByAndCountDemo;

+----------+------------+
| ClientId | ClientName |
+----------+------------+
| 1        | John       |
| 2        | Carol      |
| 3        | Sam        |
| 4        | Sam        |
| 5        | John       |
| 6        | John       |
| 7        | John       |
| 8        | Sam        |
| 9        | Sam        |
| 10       | Sam        |
| 11       | John       |
| 12       | John       |
| 13       | John       |
| 14       | David      |
| 15       | Maxwell    |
| 16       | Maxwell    |
+----------+------------+

4. 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          |
+------------+------------+

ขยาย: เรียงตามจำนวน (มากไปน้อย)

SELECT ClientName, COUNT(*) AS cnt
FROM GroupByAndCountDemo
GROUP BY ClientName
ORDER BY cnt DESC;

ขยาย: กรองเฉพาะที่มากกว่า N ครั้ง (HAVING)

SELECT ClientName, COUNT(*) AS cnt
FROM GroupByAndCountDemo
GROUP BY ClientName
HAVING COUNT(*) > 2;

MySQL 8.0+: ใช้ Window Function

SELECT DISTINCT ClientName, 
       COUNT(*) OVER (PARTITION BY ClientName) AS cnt
FROM GroupByAndCountDemo;

สรุป: GROUP BY + COUNT(*) = วิธีมาตรฐานได้ distinct values พร้อมนับจำนวนใน SQL