MySQL에서 여러 행의 개수를 집계해 단일 행의 여러 열로 표시하는 방법

개요

MySQL에서는 조건부 SUM() 함수를 활용하면 테이블에 저장된 여러 행의 데이터를 조건별로 개수를 세고, 그 결과를 하나의 행에 서로 다른 열로 나누어 표시할 수 있습니다. 이 글에서는 색상 데이터를 예제로 사용해 그 과정을 단계별로 살펴보겠습니다.

1. 테이블 생성하기

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

mysql> create table DemoTable1452
    -> (
    -> FavouriteColor varchar(50)
    -> );
Query OK, 0 rows affected (2.42 sec)

2. 데이터 삽입하기

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

mysql> insert into DemoTable1452 values('Red');
Query OK, 1 row affected (0.31 sec)
mysql> insert into DemoTable1452 values('Yellow');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1452 values('Yellow');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1452 values('Yellow');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable1452 values('Blue');
Query OK, 1 row affected (0.42 sec)
mysql> insert into DemoTable1452 values('Blue');
Query OK, 1 row affected (0.33 sec)
mysql> insert into DemoTable1452 values('Red');
Query OK, 1 row affected (0.29 sec)

3. 전체 레코드 조회하기

SELECT 문으로 테이블의 모든 레코드를 확인합니다.

mysql> select * from DemoTable1452;

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

+----------------+
| FavouriteColor |
+----------------+
| Red            |
| Yellow         |
| Yellow         |
| Yellow         |
| Blue           |
| Blue           |
| Red            |
+----------------+
7 rows in set (0.00 sec)

4. 조건별 개수를 여러 열로 집계하기

이제 핵심 쿼리입니다. MySQL에서는 비교 연산식이 참이면 1, 거짓이면 0을 반환한다는 특성을 이용해 SUM() 함수 안에 조건을 넣으면 해당 조건을 만족하는 행의 개수를 구할 수 있습니다.

mysql> select sum(FavouriteColor='Red') as NumberOfRedColor,
    -> sum(FavouriteColor='Yellow') as NumberOfYellowColor,
    -> sum(FavouriteColor='Blue') as NumberOfBlueColor
    -> from DemoTable1452;

실행 결과는 다음과 같습니다.

+------------------+---------------------+-------------------+
| NumberOfRedColor | NumberOfYellowColor | NumberOfBlueColor |
+------------------+---------------------+-------------------+
|                2 |                   3 |                 2 |
+------------------+---------------------+-------------------+
1 row in set (0.27 sec)

결과 해석

위 결과를 보면 'Red'는 2개, 'Yellow'는 3개, 'Blue'는 2개로 각각 집계되어 하나의 행에 세 개의 열로 깔끔하게 표시된 것을 확인할 수 있습니다.

참고: CASE 표현식을 활용한 대안

SUM() 대신 CASE 표현식을 사용해도 동일한 결과를 얻을 수 있으며, 가독성이 더 좋다는 장점이 있습니다.

mysql> select
    ->   sum(case when FavouriteColor = 'Red' then 1 else 0 end) as NumberOfRedColor,
    ->   sum(case when FavouriteColor = 'Yellow' then 1 else 0 end) as NumberOfYellowColor,
    ->   sum(case when FavouriteColor = 'Blue' then 1 else 0 end) as NumberOfBlueColor
    -> from DemoTable1452;

또한 COUNT() 함수와 조건을 함께 사용하는 방법도 있습니다. COUNT(조건)은 NULL이 아닌 값만 세기 때문에, 조건이 참일 때만 값을 반환하도록 작성하면 됩니다.

mysql> select
    ->   count(FavouriteColor = 'Red' or null) as NumberOfRedColor,
    ->   count(FavouriteColor = 'Yellow' or null) as NumberOfYellowColor,
    ->   count(FavouriteColor = 'Blue' or null) as NumberOfBlueColor
    -> from DemoTable1452;

마무리

이처럼 MySQL의 SUM(), COUNT(), CASE 표현식을 활용하면 별도의 서브쿼리나 애플리케이션 로직 없이도 여러 조건의 개수를 한 번의 쿼리로 집계하고, 피벗 형태로 단일 행에 표시할 수 있습니다. 카테고리별 통계나 대시보드용 요약 데이터를 만들 때 매우 유용하게 활용할 수 있는 기법이니 꼭 기억해 두세요.