집계 함수와 GROUP BY

게시글 100행을 그대로 읽어 애플리케이션에서 합계를 낼 수도 있지만, 데이터베이스는 행의 집합을 그룹으로 묶고 그룹마다 한 행으로 줄일 수 있습니다. 문제는 어떤 단위로 묶어 무엇을 세는지가 분명하지 않으면, 그럴듯한 틀린 답이 쉽게 나온다는 점입니다.

이 글에서는 전용 테이블 ch4_aggregate_post_demo에서 전체 게시글 수와 게시 시각이 있는 글의 수, 총 조회수, 평균과 최댓값, 최솟값을 계산하고 회원별로 묶습니다. 원래 행의 제목을 그룹의 결과에 섞는 오류를 통해, ONLY_FULL_GROUP_BY가 왜 보호 장치인지도 확인합니다.

집계 예제 데이터

회원별 건수와 합계를 고정하기 위해 일곱 행을 전용 테이블에 만듭니다. 회원 1은 공개 글 세 개와 초안 하나를, 회원 2는 공개 글 두 개와 초안 하나를 가집니다.

DROP TABLE IF EXISTS ch4_aggregate_post_demo;
CREATE TABLE ch4_aggregate_post_demo (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  author_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(80) NOT NULL,
  status VARCHAR(16) NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL,
  published_at DATETIME(6) NULL
) ENGINE = InnoDB;

INSERT INTO ch4_aggregate_post_demo
  (id, author_id, title, status, view_count, created_at, published_at)
VALUES
  (1, 1, 'SELECT 기초', 'PUBLISHED', 30, '2026-07-10 09:00:00', '2026-07-10 09:30:00'),
  (2, 1, 'WHERE 조건', 'PUBLISHED', 50, '2026-07-11 09:00:00', '2026-07-11 10:00:00'),
  (3, 1, 'GROUP BY', 'PUBLISHED', 55, '2026-07-12 12:00:00', '2026-07-12 13:00:00'),
  (4, 1, '집계 초안', 'DRAFT', 60, '2026-07-13 15:00:00', NULL),
  (5, 2, 'JOIN 기초', 'PUBLISHED', 40, '2026-07-11 10:00:00', '2026-07-11 10:30:00'),
  (6, 2, 'NULL 정리', 'PUBLISHED', 45, '2026-07-12 11:00:00', '2026-07-12 11:30:00'),
  (7, 2, '통계 초안', 'DRAFT', 25, '2026-07-13 13:00:00', NULL);

그룹에 속하지 않은 열

회원별 합계를 만들면서 title도 함께 SELECT해 봅니다. 한 회원에게 게시글이 여러 개 있는데 어느 제목을 보여 줄지 정한 규칙이 없으므로, 이 열은 그룹 결과의 한 행과 단위가 맞지 않습니다.

SELECT
  author_id,
  title,
  SUM(view_count) AS total_views
FROM ch4_aggregate_post_demo
GROUP BY author_id;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'board_lab.ch4_aggregate_post_demo.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

MySQL 8.4의 기본 SQL 모드에 든 ONLY_FULL_GROUP_BY가 이 모호한 선택을 거절했습니다. GROUP BY 뒤의 결과 한 행은 회원 그룹 전체를 대표하는데, SUM은 여러 값을 하나로 줄여도 제목은 여러 개 그대로 남기 때문입니다. 이 모드를 끄면 아무 제목이나 보일 수 있지만, 그렇다고 뜻이 정해지는 것은 아닙니다.

대표 제목이 필요하다면 최신 글, 가장 긴 글, 사전 순서의 첫 글 같은 고르는 규칙을 쿼리로 따로 적어야 합니다. 어느 값이든 상관없다는 것이 업무상 분명하다면 MySQL의 ANY_VALUE(title)로 그 의도를 밝힐 수도 있습니다. GROUP BY에 기본 키나 NULL이 없는 UNIQUE 열을 두면, MySQL은 같은 테이블의 다른 열이 그 키로 정해진다는 것을 알아보므로 그 열들은 집계하지 않고도 SELECT할 수 있습니다.

집계 함수가 세는 것

COUNT(*)는 그룹의 행 수를 세고, COUNT(열)은 그 열에서 NULL이 아닌 값의 수를 셉니다. SUM과 AVG는 NULL을 빼고 계산하며, NULL이 아닌 값이 하나도 없으면 NULL을 돌려줍니다. MIN과 MAX는 숫자뿐 아니라 날짜에도 써서 처음과 마지막 시각의 범위를 구할 수 있습니다.

GROUP BY 없이 입력 전체를 집계하면, 입력이 비어 있어도 결과는 한 행입니다. 다음 쿼리는 조건에 맞는 행이 하나도 없는 입력을 집계합니다.

SELECT
  COUNT(*) AS row_count,
  COUNT(published_at) AS published_count,
  SUM(view_count) AS total_views,
  AVG(view_count) AS average_views
FROM ch4_aggregate_post_demo
WHERE id > 100;

row_count | published_count | total_views | average_views
0         | 0               | NULL        | NULL

건수는 0이지만 합계와 평균은 NULL입니다. 합계를 0으로 보여 주어야 하는 화면이라면 COALESCE(SUM(view_count), 0)처럼 그 규칙을 식에 적습니다. 같은 쿼리에 GROUP BY를 붙이면 만들 그룹이 없으므로 결과 행 자체가 나오지 않는다는 차이도 기억해 둡니다.

GROUP BY에 적은 열의 조합은 결과 한 행의 식별자가 되므로, SELECT의 집계하지 않은 표현식은 모두 그 조합으로 값이 정해져야 합니다. 기본적인 집계 함수와 GROUP BY는 표준 SQL입니다. MySQL은 SELECT의 별칭을 GROUP BY와 HAVING에서도 쓸 수 있게 넓혔지만, 다른 DBMS로 옮길 쿼리라면 원래의 표현식을 쓰거나 단계를 분명히 나눕니다. 이 교재는 ONLY_FULL_GROUP_BY를 끄지 않습니다.

회원별 게시글 통계

회원 id를 그룹의 키로 두고 나머지 결과는 모두 집계합니다. 게시된 글의 수는 published_at에서 NULL이 아닌 값을 세어 전체 행 수와 비교합니다.

SELECT
  author_id,
  COUNT(*) AS post_count,
  COUNT(published_at) AS published_count,
  SUM(view_count) AS total_views,
  ROUND(AVG(view_count), 1) AS average_views,
  MIN(created_at) AS first_created_at,
  MAX(created_at) AS last_created_at
FROM ch4_aggregate_post_demo
GROUP BY author_id
ORDER BY total_views DESC, author_id;

author_id | post_count | published_count | total_views | average_views | first_created_at           | last_created_at
1         | 4          | 3               | 195         | 48.8          | 2026-07-10 09:00:00.000000 | 2026-07-13 15:00:00.000000
2         | 3          | 2               | 110         | 36.7          | 2026-07-11 10:00:00.000000 | 2026-07-13 13:00:00.000000

회원마다 정확히 한 행이 나오고, 전체 글과 게시된 글의 차이도 보입니다. 회원 1의 평균은 195를 4로 나눈 48.75를 반올림한 48.8이고, 회원 2는 110을 3으로 나눈 36.6667을 반올림한 36.7입니다. MySQL의 SUM과 AVG는 정수를 받으면 DECIMAL을 돌려주므로, 합계가 원래 열의 범위를 넘어도 결과에 담깁니다.

이 평균에는 초안의 조회수도 들어 있습니다. 게시된 글의 평균 조회수만 필요하다면 WHERE에서 게시된 행만 남기거나 조건을 넣은 집계를 써야 합니다. 통계의 이름에 어떤 집합을 대상으로 했는지 드러내지 않으면, 같은 AVG를 팀마다 다른 뜻으로 쓰게 됩니다.

더 깊이: GROUP BY는 정렬을 약속하지 않는다

MySQL 5.7까지는 GROUP BY가 특정 조건에서 결과를 그룹 키의 순서로 정렬했고, GROUP BY author_id DESC처럼 정렬 방향을 적는 비표준 문법도 있었습니다. 그런데 옵티마이저가 임시 테이블로 그룹을 만들 때는 정렬이 필요 없는데도 정렬을 해야 했고, 이를 피하려면 ORDER BY NULL이라는 또 다른 비표준 문법을 붙여야 했습니다. 그래서 MySQL 8.0에서 이 암묵적 정렬이 없어졌고, 8.0.13에서는 GROUP BY의 ASC와 DESC 문법도 제거되었습니다(MySQL 블로그). 예전 버전에서 그룹 키 순서로 나오던 결과가 8.4에서는 다른 순서로 나올 수 있으므로, 순서가 필요하면 위의 쿼리처럼 ORDER BY를 따로 적습니다.

그룹을 만드는 가장 일반적인 방법은 테이블을 모두 읽어, 그룹마다 행이 이어지도록 임시 테이블을 만든 뒤 집계하는 것입니다. 하지만 GROUP BY의 열이 모두 한 인덱스에 속하고 그 인덱스가 B+Tree처럼 키를 정렬해 저장한다면, 정렬된 인덱스를 따라 읽으며 임시 테이블 없이 그룹을 나눌 수 있습니다. 범위 안의 키를 모두 읽는 이 방식을 빈틈없는 인덱스 스캔(tight index scan)이라고 합니다.

그룹마다 인덱스의 일부 키만 골라 읽는 느슨한 인덱스 스캔(loose index scan)은 이보다 더 적게 읽습니다. 다만 쿼리가 테이블 하나만 읽고, GROUP BY가 인덱스의 왼쪽 접두어 열만 쓰며, 집계 함수가 같은 열의 MIN()과 MAX()뿐일 때만 쓸 수 있고, 이때 EXPLAIN의 Extra에 Using index for group-by가 나옵니다. 이 글처럼 SUM과 AVG를 구하는 쿼리는 그룹 안의 모든 행을 읽어야 하므로 이 방식의 대상이 아닙니다.

원래 합계와 그룹 합계를 비교한다

그룹 통계를 만든 뒤 원래 행의 합계와 그룹별 합계를 다시 더한 값을 비교하면, 빠진 필터나 중복된 조인을 찾는 기준이 생깁니다. 아래 쿼리는 6장에서 배울 서브쿼리를 미리 빌려 쓴 진단 도구입니다.

SELECT
  (SELECT SUM(view_count) FROM ch4_aggregate_post_demo) AS raw_total,
  SUM(group_total) AS regrouped_total
FROM (
  SELECT author_id, SUM(view_count) AS group_total
  FROM ch4_aggregate_post_demo
  GROUP BY author_id
) AS author_totals;

raw_total | regrouped_total
305       | 305

두 값은 모두 305입니다. 합계가 어긋나면 오류를 찾을 단서가 되지만, 합계가 같다는 것만으로 누락과 중복이 없다고 증명되지는 않습니다. 조인을 더한 집계에서 값이 커지면, 일대다 관계로 행이 불어난 것부터 의심합니다.

평균의 평균은 그룹마다 행의 수가 다르면 전체 평균과 같지 않습니다. 회원별 평균 48.75와 36.6667을 다시 단순 평균하면 약 42.7이지만, 전체 평균은 305를 7로 나눈 약 43.6입니다. 회원별 평균을 다시 AVG하면 게시글이 하나인 회원과 백 개인 회원이 같은 무게를 가지므로, 전체 평균이 필요하면 전체의 합계를 건수로 나누거나 가중 평균을 씁니다.

집계에서 따질 것

집계를 시험할 때는 게시글이 없는 회원, 글은 있지만 게시 시각이 모두 NULL인 회원, 조회수가 0인 글과 아주 큰 글을 넣습니다. 5장 3편의 LEFT JOIN 뒤에 COUNT(*)를 쓰면 부모 행 하나도 세므로, 자식이 없는데 1이 나오는 함정이 있습니다. 그때는 COUNT(child.id)처럼 자식의 기본 키를 셉니다.

GROUP BY에 날짜를 넣을 때는 서버의 시간대와 사용자의 날짜 범위가 다르면 일별 통계가 어긋납니다. 어느 시간대를 기준으로 날짜를 나눌지를 데이터의 규칙에 넣고, 월별 통계라면 사용자의 시간대에서 월의 시작과 끝을 계산한 뒤 UTC의 반열린 구간으로 조회합니다. 원래 데이터가 지워지거나 고쳐졌을 때 지난달의 통계를 다시 계산할지도 함께 정합니다.

대시보드의 숫자 카드들은 원래 데이터가 바뀌는 동안 서로 다른 시점을 볼 수 있습니다. 여러 수치를 한 쿼리에서 만들지, 같은 트랜잭션의 스냅샷으로 읽을지, 약간의 차이를 받아들일지 정합니다. 결과를 자주 요청하는데 원래 데이터가 크다면 미리 집계해 둔 테이블을 검토하되, 다시 계산해도 같은 결과가 나오는 방법과 지연을 허용할 범위는 16장에서 함께 설계합니다.

평균이나 비율을 저장할 때는 분자와 분모도 함께 남겨야 다시 집계할 수 있습니다. 평균값만 모아서는 전체 평균을 재현할 수 없고 반올림의 오차도 쌓이므로, total_views와 post_count를 함께 두면 필요할 때 가중 평균을 다시 계산할 수 있습니다. 집계한 행에는 만든 시각과 원래 데이터의 범위도 기록해, 사용자가 숫자가 얼마나 최신인지 판단하게 합니다.

COUNT(DISTINCT author_id)처럼 집계 안에 DISTINCT를 넣으면 서로 다른 값의 수를 세지만, 중복이 왜 생겼는지부터 확인합니다. 통계의 숫자는 함수보다 입력 집합의 정의에서 더 자주 틀립니다. 새 상태가 생기거나 게시글의 삭제 규칙이 바뀌면 모집단의 정의도 고쳐야 하므로, SQL 파일만 복사해 두지 말고 표본 입력과 예상 행 수, 합계의 일치 여부를 함께 남깁니다.

일별, 태그별처럼 그룹의 축을 늘리면 결과 한 행을 식별하는 키도 함께 늘어납니다. author_id와 post_date가 한 행을 식별한다면, 그 조합을 데이터의 규칙과 나중에 만들 집계 테이블의 유일성 제약에 반영합니다. 집계를 배우는 것은 함수를 외우는 일이라기보다, 결과 한 행이 무엇인지 설계하는 첫 연습입니다.

GROUP BY를 쓰기 전에 결과 표의 기본 키를 말로 적어 봅니다. "회원마다 한 행"이라면 author_id가 그룹의 키이고, 다른 열은 집계되거나 그 키로 값이 정해져야 합니다.

그룹 키와 HAVING을 작은 데이터로 확인한다

등급별 고객 수와 평균 포인트를 구한다면 결과 한 행은 등급 하나를 대표합니다. 다음 예제는 별도의 실습 데이터베이스 cs_db_lab에 고객 세 명을 두고, 그룹마다 집계한 뒤 다음 글에서 자세히 볼 HAVING으로 그룹을 거릅니다.

CREATE DATABASE IF NOT EXISTS cs_db_lab;
DROP TABLE IF EXISTS cs_db_lab.cs_group_customer;
CREATE TABLE cs_db_lab.cs_group_customer (
  id INT PRIMARY KEY,
  tier VARCHAR(20) NOT NULL,
  points INT NOT NULL
);
INSERT INTO cs_db_lab.cs_group_customer VALUES (1, 'gold', 1500), (2, 'gold', 2500), (3, 'silver', 500);

SELECT tier, COUNT(*) AS customer_count, AVG(points) AS average_points
FROM cs_db_lab.cs_group_customer
GROUP BY tier
HAVING AVG(points) >= 2000
ORDER BY tier;

tier | customer_count | average_points
gold | 2              | 2000.0000

입력은 gold 두 행과 silver 한 행입니다. 묶은 뒤 gold는 2명에 평균 2000, silver는 1명에 평균 500이고, HAVING을 지나면 gold 한 행만 남습니다. 정수의 평균은 소수 넷째 자리까지의 DECIMAL로 나옵니다. FROM에서 읽은 행, 그룹별 중간 결과, HAVING 뒤의 결과를 따로 적어 보면 문법의 오류와 뜻의 오류를 구별하기 쉽습니다.

정리

• 집계는 여러 입력 행을 그룹마다 한 결과 행으로 바꿉니다.
• COUNT(*)는 행을, COUNT(열)은 NULL이 아닌 값을 세며, SUM과 AVG는 NULL을 빼고 계산합니다.
• 집계하지 않은 열은 그룹의 키로 값이 하나로 정해져야 하며, ONLY_FULL_GROUP_BY가 이를 검사합니다.
• 원래 합계와 그룹 합계를 비교해 누락과 행의 증가를 점검하고, 평균의 평균 대신 전체의 합계와 건수로 평균을 구합니다.

연습 문제

1. 작성 날짜별 공개 글 통계

공개된 글만 대상으로 작성 날짜별 게시글 수와 서로 다른 작성자 수, 총 조회수, 평균 조회수를 계산해 보세요. 날짜는 DATE(created_at)으로 구하고 최근 날짜부터 정렬합니다. 같은 모집단의 원래 합계와 날짜별 합계의 합도 비교합니다.

SELECT
  DATE(created_at) AS post_date,
  COUNT(*) AS post_count,
  COUNT(DISTINCT author_id) AS author_count,
  SUM(view_count) AS total_views,
  ROUND(AVG(view_count), 1) AS average_views
FROM ch4_aggregate_post_demo
WHERE status = 'PUBLISHED'
GROUP BY DATE(created_at)
ORDER BY post_date DESC;

post_date  | post_count | author_count | total_views | average_views
2026-07-12 | 2          | 2            | 100         | 50.0
2026-07-11 | 2          | 2            | 90          | 45.0
2026-07-10 | 1          | 1            | 30          | 30.0

SELECT SUM(view_count) AS published_raw_total
FROM ch4_aggregate_post_demo
WHERE status = 'PUBLISHED';

published_raw_total
220

2. WHERE로 먼저 거르면

등급 예제에서 WHERE points >= 2000을 먼저 적용한 뒤 같은 GROUP BY를 하면, gold의 고객 수와 평균은 얼마가 되나요? 본문의 HAVING 결과와 무엇이 다른가요?

3. 기본 키로 묶으면

본문의 오류 쿼리를 GROUP BY id로 바꾸면 title을 집계하지 않고도 SELECT할 수 있습니다. 왜 그런가요? 그 결과는 회원별 통계로 쓸 수 있나요?

다음 글 HAVING과 실행 순서에서는 원래의 행을 거르는 WHERE와 그룹의 결과를 거르는 HAVING이 쿼리의 어느 단계에서 실행되는지 구별합니다.