SQLite 집계 함수 완벽 정리: COUNT, SUM, AVG, MIN, MAX

집계 함수가 실제로 하는 일

지금까지 봐온 SQL 함수 대부분은 행 단위로 동작합니다. UPPER(name)은 행마다 한 번씩 실행되고, ROUND(price, 2)도 마찬가지죠. 반면 sqlite 집계 함수는 다르게 동작합니다. 여러 행으로 이루어진 집합 전체를 보고, 그걸 하나의 값으로 압축해 버립니다.

직접 다뤄볼 수 있는 작은 테이블을 하나 만들어 봅시다:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada',   42.00, 'paid'),
    ('Boris', 15.50, 'paid'),
    ('Ada',   88.00, 'paid'),
    ('Carl',  10.00, 'refunded'),
    ('Ada',   NULL,  'pending');

SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

다섯 줄이 들어가서 한 줄이 나온다. 집계 함수의 핵심은 이 한 문장으로 요약된다. 여러 행을 하나의 요약값으로 압축하는 것이다. GROUP BY 없이 쓰면 결과 집합 전체가 하나의 요약 대상이 된다.

COUNT 함수: 행을 셀까, 값을 셀까

sqlite count 함수는 세 가지 형태가 있는데, 이 차이를 제대로 이해하는 게 중요하다:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Ada',   88.00),
    ('Carl',  NULL),
    ('Ada',   NULL);

SELECT
    COUNT(*)               AS rows_total,
    COUNT(amount)          AS rows_with_amount,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

• COUNT(*)는 행의 개수를 셉니다. NULL도 포함하고, 결과는 항상 숫자입니다.
• COUNT(column)은 해당 컬럼에서 NULL이 아닌 값만 셉니다.
• COUNT(DISTINCT column)은 NULL을 제외한 고유한 값의 개수를 셉니다.

위 예시에서 행은 5개지만 그중 amount가 채워진 건 3개이고, 서로 다른 고객도 3명입니다. COUNT(amount) 값이 COUNT(*)보다 작게 나와서 의아했다면 이유는 간단합니다. NULL은 세지 않거든요.

SUM, AVG, MIN, MAX 함수 사용법

산술 집계 함수들은 예상대로 동작하지만, 한 가지 조용한 규칙이 있습니다. 모두 NULL 값은 무시한다는 점이죠:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT
    SUM(amount) AS total,
    AVG(amount) AS mean,
    MIN(amount) AS smallest,
    MAX(amount) AS largest,
    COUNT(amount) AS counted
FROM orders;

AVG는 (10 + 20 + 30) / 3 = 20.0이지, 60 / 4 = 15.0이 아닙니다. 분모는 NULL이 아닌 값들의 개수이기 때문이죠. 만약 이런 동작을 원하지 않는다면, 즉 누락된 데이터를 0으로 처리하고 싶다면 다음과 같이 명시적으로 작성해야 합니다:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT AVG(COALESCE(amount, 0)) FROM orders;

MIN과 MAX는 텍스트와 날짜에도 쓸 수 있는데, 텍스트는 사전순으로, 날짜는 표준 ISO 문자열 형태로 비교합니다.

sqlite total vs sum 차이

SQLite에는 SUM 말고도 합계를 구하는 집계 함수가 하나 더 있습니다. 바로 TOTAL인데요, SUM이 가진 두 가지 짜증나는 점을 해결해 줍니다:

CREATE TABLE empty_table (amount REAL);

SELECT SUM(amount), TOTAL(amount) FROM empty_table;

• SUM은 행이 하나도 없으면 NULL을 돌려주지만, TOTAL은 0.0을 반환합니다.
• 값이 전부 NULL일 때도 SUM은 NULL, TOTAL은 0.0이 나옵니다.
• TOTAL은 항상 실수형(floating-point)을 반환하므로 정수 연산 오버플로가 발생할 일이 없습니다.

대신 트레이드오프가 있습니다. TOTAL은 표준 SQL이 아니고, 결과가 무조건 REAL 타입이라서 정수를 기대했다면 당황할 수 있죠. "행이 없으면 0이 맞다"가 앱의 자연스러운 동작이라면 TOTAL을, SQL 표준 동작을 따르고 싶다면 SUM을 쓰면 됩니다.

집계 함수 안에서 DISTINCT 활용하기

DISTINCT는 COUNT뿐 아니라 어떤 집계 함수에도 함께 쓸 수 있습니다. 집계가 수행되기 전에 중복 값을 먼저 걸러내죠:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Carl',  10.00);

SELECT
    SUM(amount)          AS sum_all,
    SUM(DISTINCT amount) AS sum_unique,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

SUM(amount)은 모든 행의 amount를 더합니다. 반면 SUM(DISTINCT amount)은 중복을 제거한 고유 값만 한 번씩 더하죠. "고유한 청구 금액의 합계" 같은 경우엔 쓸모가 있지만, 실무에서 원하는 동작은 거의 아닙니다. 정작 자주 쓰이는 건 COUNT(DISTINCT customer) 쪽입니다.

FILTER 절로 일부 행만 집계하기

특정 행만 골라서 집계하고 싶을 때 가장 먼저 떠오르는 건 WHERE죠. 그런데 WHERE는 쿼리 전체에 적용되기 때문에, 같은 쿼리 안에서 "결제 완료 주문 수"와 "환불 건수"를 한 번에 세는 건 불가능합니다. 이럴 때 쓰는 게 바로 sqlite의 FILTER 절입니다:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'),
    (15.50, 'paid'),
    (88.00, 'paid'),
    (10.00, 'refunded'),
    (25.00, 'pending');

SELECT
    COUNT(*) FILTER (WHERE status = 'paid')      AS paid_count,
    COUNT(*) FILTER (WHERE status = 'refunded')  AS refund_count,
    SUM(amount) FILTER (WHERE status = 'paid')   AS paid_revenue
FROM orders;

FILTER (WHERE ...) 절은 바로 그 앞의 집계 함수 하나에만 적용됩니다. 테이블을 한 번만 훑으면서 여러 조건의 결과를 동시에 뽑아낼 수 있다는 게 핵심이죠. FILTER가 도입되기 전에는 같은 효과를 내려고 SUM(CASE WHEN status = 'paid' THEN amount END) 같은 식을 썼습니다. 동작은 똑같지만, 손이 더 많이 갈 수밖에 없었어요.

GROUP_CONCAT: 문자열로 묶어주는 집계 함수

GROUP_CONCAT은 다른 집계 함수들과 결이 좀 다릅니다. 숫자를 돌려주는 게 아니라, 값들을 하나의 문자열로 이어 붙여 주거든요:

CREATE TABLE tags (post_id INTEGER, tag TEXT);
INSERT INTO tags VALUES
    (1, 'sqlite'),
    (1, 'database'),
    (1, 'tutorial'),
    (2, 'python'),
    (2, 'sqlite');

SELECT
    post_id,
    GROUP_CONCAT(tag)        AS default_separator,
    GROUP_CONCAT(tag, ' | ') AS pipe_separated
FROM tags
GROUP BY post_id;

기본 구분자는 쉼표입니다. 다른 문자를 쓰고 싶다면 두 번째 인자로 넘겨주면 됩니다. 다만 순서는 보장되지 않으니, 정렬이 필요할 땐 GROUP_CONCAT(tag ORDER BY tag) 형태로 감싸 주세요. 결과가 UI에 노출될 때 순서를 일정하게 유지하고 싶다면 특히 유용합니다.

GROUP BY 없이 집계 함수 사용하기

지금까지 GROUP BY 없이 집계 함수를 쓴 예제들은 모두 결과가 딱 한 행이었습니다. 이게 규칙입니다. GROUP BY가 없는 SELECT에 집계 함수가 들어가면, (WHERE가 적용된 뒤) 테이블 전체를 요약한 단일 행이 나옵니다.

여러 집계 함수를 자유롭게 섞어 쓸 수도 있습니다:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'), (15.50, 'paid'), (88.00, 'paid'),
    (10.00, 'refunded'), (25.00, 'pending');

SELECT
    COUNT(*)    AS orders,
    SUM(amount) AS revenue,
    AVG(amount) AS average,
    MIN(amount) AS smallest,
    MAX(amount) AS largest
FROM orders
WHERE status = 'paid';

단, 비집계 컬럼과 집계 함수를 섞어 쓰면서 의미 있는 결과를 기대하는 건 안 됩니다:

-- SQLiteでは許可されているが、`customer` の値は不定である。
SELECT customer, SUM(amount) FROM orders;

SQLite는 이 경우 에러를 내지 않지만(다른 DB들은 에러를 냅니다), 합계 옆에 보여줄 고객 이름을 아무거나 하나 골라서 출력해 버립니다. 고객별로 합계를 구하고 싶다면 GROUP BY가 필요한데, 바로 다음 페이지에서 다룹니다.

다음 주제: GROUP BY와 HAVING

테이블 전체에 대한 집계는 "총합이 얼마냐"라는 질문에 답합니다. 반면에 그룹별 집계 — 고객별, 월별, 상태별 — 는 훨씬 더 흥미로운 질문에 답해 줍니다. GROUP BY는 집계하기 전에 행을 버킷 단위로 쪼개는 방법이고, HAVING은 집계된 결과를 필터링하는 방법입니다. 이게 다음 이야기입니다.