MySQL에서 성(LastName)별 레코드 개수를 나타내는 Count 열 추가하는 방법

MySQL에서 각 성(LastName)이 몇 번 등장하는지 계산한 Count 열을 SELECT 쿼리 결과에 함께 출력하는 방법을 알아보겠습니다. 서브쿼리와 GROUP BY, JOIN을 활용하면 손쉽게 구현할 수 있습니다.

1. 테이블 생성하기

먼저 예제로 사용할 테이블을 생성합니다.

mysql> create table DemoTable
(
    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    FirstName varchar(100),
    LastName varchar(100)
);
Query OK, 0 rows affected (1.00 sec)

2. 샘플 데이터 삽입하기

INSERT 명령어를 사용해 테이블에 레코드를 추가합니다.

mysql> insert into DemoTable(FirstName,LastName) values('David','Miller');
Query OK, 1 row affected (0.35 sec)
mysql> insert into DemoTable(FirstName,LastName) values('Carol','Miller');
Query OK, 1 row affected (0.37 sec)
mysql> insert into DemoTable(FirstName,LastName) values('John','Doe');
Query OK, 1 row affected (0.21 sec)

3. 전체 레코드 조회하기

SELECT 문으로 테이블의 모든 레코드를 확인해 보겠습니다.

mysql> select *from DemoTable;

위 쿼리는 다음과 같은 결과를 출력합니다.

+----+-----------+----------+
| Id | FirstName | LastName |
+----+-----------+----------+
|  1 | David     | Miller   |
|  2 | Carol     | Miller   |
|  3 | John      | Doe      |
+----+-----------+----------+
3 rows in set (0.00 sec)

4. Count 열이 포함된 쿼리 작성하기

이제 각 성(LastName)별 등장 횟수를 계산하여 결과에 함께 표시하는 쿼리입니다. 예를 들어 "Miller"는 2번 등장하므로 해당 행들의 Count 값은 2가 됩니다.

핵심은 GROUP BY로 성별 개수를 집계한 서브쿼리를 만들고, 이를 원본 테이블과 LastName 기준으로 JOIN하는 것입니다.

mysql> select Id, FirstName, LastName,Count from DemoTable tbl1
   JOIN (select count(*) as Count, LastName from DemoTable GROUP BY LastName) tbl2
   using(LastName);

위 쿼리는 다음과 같은 결과를 출력합니다.

+----+-----------+----------+-------+
| Id | FirstName | LastName | Count |
+----+-----------+----------+-------+
|  1 | David     | Miller   |     2 |
|  2 | Carol     | Miller   |     2 |
|  3 | John      | Doe      |     1 |
+----+-----------+----------+-------+
3 rows in set (0.00 sec)

정리

결과를 보면 "Miller"라는 성이 2번 등장했기 때문에 David와 Carol 행의 Count 값은 2로 표시되고, "Doe"는 1번만 등장했으므로 John 행의 Count 값은 1로 표시됩니다.

이처럼 GROUP BY로 집계한 서브쿼리를 JOIN하면, 원본 레코드는 그대로 유지하면서도 각 그룹별 집계 값을 추가 열 형태로 함께 조회할 수 있습니다.