상태별 건수를 한 행에 표현하기
고객별 전체 주문 수와 결제·취소 주문 수를 나란히 조회하려고 한다. WHERE status = 'paid'로 먼저 제한하면 취소 주문이 집계 대상에서 사라진다. 서로 다른 조건의 건수를 같은 행에 표시하려면 조건부 집계를 사용할 수 있다.
예제는 MySQL 8.4에서 사용할 수 있는 문법으로 작성했으며, 아래 결과는 SQLite에서 실행해 검증했다. MySQL 실행 계획이나 성능을 측정한 예제는 아니다.
실습 데이터
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
status VARCHAR(20)
);
INSERT INTO orders VALUES
(1, 10, 'paid'),
(2, 10, 'paid'),
(3, 10, 'cancelled'),
(4, 20, 'pending'),
(5, 20, NULL),
(6, 30, 'cancelled');CASE로 1과 0을 만든 뒤 더한다
SELECT customer_id,
COUNT(*) AS total_count,
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count,
SUM(CASE WHEN status IS NULL THEN 1 ELSE 0 END) AS unknown_count
FROM orders
GROUP BY customer_id
ORDER BY customer_id;| customer_id | total_count | paid_count | cancelled_count | unknown_count |
|---|---|---|---|---|
| 10 | 3 | 2 | 1 | 0 |
| 20 | 2 | 0 | 0 | 1 |
| 30 | 1 | 0 | 1 | 0 |
결제 건수의 CASE 식은 결제 행에서 1, 나머지 행에서 0이 된다. 고객별로 이 값을 더하면 조건에 맞는 건수만 남는다. NULL 여부는 = NULL이 아니라 IS NULL로 검사한다.
20번 고객의 전체 주문은 2건이지만 표시한 상태별 건수의 합은 1이다. pending을 별도 열로 집계하지 않았기 때문이다. 일부 상태만 집계했다면 그 합이 전체 건수와 같을 것이라고 가정하면 안 된다.
COUNT와 SUM에 같은 식을 넣으면 안 된다
SELECT
COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS wrong_count,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_count
FROM orders;| wrong_count | paid_count |
|---|---|
| 6 | 2 |
COUNT는 0도 NULL이 아닌 값으로 센다. 따라서 첫 번째 식은 모든 주문을 센다. 두 번째 식은 ELSE를 생략했으므로 미일치 행에서 NULL이 되고, COUNT의 대상에서 제외된다.
SUM으로 1과 0을 더하는 방식과 COUNT로 1과 NULL을 세는 방식은 구분해서 읽어야 한다.
입력이 아예 없을 때
SELECT
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS raw_sum,
COALESCE(SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END), 0) AS paid_count,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_count_by_count
FROM orders
WHERE customer_id = 999;| raw_sum | paid_count | paid_count_by_count |
|---|---|---|
| NULL | 0 | 0 |
행이 있지만 조건에 맞지 않는 경우와, 집계할 행 자체가 없는 경우는 다르다. 후자의 SUM은 NULL이므로 0을 표시하려면 COALESCE 같은 처리가 필요하다. 이 쿼리는 GROUP BY가 없어 집계 결과 한 행을 반환한다.
반면 첫 쿼리의 GROUP BY에는 주문이 없는 고객의 그룹 자체가 없다. COALESCE만 추가한다고 그런 고객 행이 생기지는 않는다. 고객 전체가 필요하면 고객 테이블을 기준으로 한 LEFT JOIN 등을 별도로 설계해야 한다.
참고
