MySQL에서 컬럼 값별 행 개수 세기 (GROUP BY + COUNT)

GROUP BY 컬럼과 COUNT(*)로 각 값이 몇 번 나타나는지 쉽게 셀 수 있습니다.

테이블 생성 및 데이터 삽입

mysql> CREATE TABLE DemoTable1818 (
    -> Id INT,
    -> Name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO DemoTable1818 VALUES(10,'Chris');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO DemoTable1818 VALUES(11,'Chris');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO DemoTable1818 VALUES(11,'Chris');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO DemoTable1818 VALUES(12,'Chris');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO DemoTable1818 VALUES(10,'Chris');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO DemoTable1818 VALUES(10,'Chris');
Query OK, 1 row affected (0.00 sec)

데이터 조회

mysql> SELECT * FROM DemoTable1818;

+------+-------+
| Id   | Name  |
+------+-------+
|   10 | Chris |
|   11 | Chris |
|   11 | Chris |
|   12 | Chris |
|   10 | Chris |
|   10 | Chris |
+------+-------+
6 rows in set (0.00 sec)

Id별 개수 집계

mysql> SELECT Id, COUNT(*) AS TotalPerId FROM DemoTable1818 GROUP BY Id;

+------+------------+
| Id   | TotalPerId |
+------+------------+
|   10 |          3 |
|   11 |          2 |
|   12 |          1 |
+------+------------+
3 rows in set (0.00 sec)

응용: SELECT Name, COUNT(*) FROM ... GROUP BY Name HAVING COUNT(*) > 1로 중복 이름만 찾기. COUNT(DISTINCT 컬럼)로 고유값 개수 세기. 인덱스가 있으면 GROUP BY 성능 향상.