SQLite § aggregation

Aggregation and grouping

An aggregate function takes a set of rows and produces a single value: COUNT(*) returns how many rows there are, SUM(col) returns the total of a column, AVG(col) returns the mean. Combined with GROUP BY, aggregates collapse a table into one row per group; combined with HAVING, the grouped rows are filtered. This page documents the standard aggregates, the SQLite-specific extensions, the NULL handling that recurs through them, the FILTER clause, and the conventional patterns for grouped queries.

The standard aggregates

Function	Returns
`COUNT(*)`	The number of rows.
`COUNT(col)`	The number of rows where `col` is not NULL.
`COUNT(DISTINCT col)`	The number of distinct non-NULL values of `col`.
`SUM(col)`	The sum of non-NULL values; NULL if all are NULL or no rows.
`TOTAL(col)`	The sum of non-NULL values; 0.0 if all are NULL or no rows.
`AVG(col)`	The arithmetic mean of non-NULL values; NULL if no non-NULL rows.
`MIN(col)`	The minimum value (NULL excluded).
`MAX(col)`	The maximum value (NULL excluded).
`GROUP_CONCAT(col, sep)`	The concatenation of non-NULL values, separated by `sep` (default comma).
`STRING_AGG(col, sep)`	Synonym for `GROUP_CONCAT` (3.44+).

SELECT
    COUNT(*)                        AS total_rows,
    COUNT(email)                    AS rows_with_email,
    COUNT(DISTINCT email_domain)    AS distinct_domains,
    SUM(amount)                     AS revenue,
    AVG(amount)                     AS mean_order,
    MIN(amount)                     AS smallest,
    MAX(amount)                     AS largest,
    GROUP_CONCAT(name, '; ')        AS roster
FROM customer JOIN bouquet_order USING (customer_id);

The aggregates are the standard ones of ISO SQL with two SQLite-specific notes: TOTAL returns 0.0 instead of NULL on an empty input, which is convenient when downstream arithmetic should not be poisoned by NULL; and GROUP_CONCAT accepts a separator argument (the standard STRING_AGG accepts the same surface and is preferred in new code).

NULL in aggregates

The recurring rule: aggregate functions ignore NULL, except COUNT(*) which counts every row including NULL-bearing rows.

CREATE TABLE temperature (id INTEGER, value REAL);
INSERT INTO temperature VALUES (1, 20.0), (2, NULL), (3, 22.0);

SELECT
    COUNT(*)        AS rows_total,    -- 3
    COUNT(value)    AS rows_measured, -- 2 (NULL excluded)
    SUM(value)      AS sum,           -- 42.0
    AVG(value)      AS mean,          -- 21.0  ((20 + 22) / 2)
    MIN(value)      AS min,           -- 20.0
    MAX(value)      AS max            -- 22.0
FROM temperature;

Edge cases:

SUM of an empty set or all-NULL set returns NULL. Use TOTAL (or COALESCE(SUM(col), 0)) for 0.0.
AVG of all NULL returns NULL.
MIN and MAX of all NULL return NULL.
COUNT(*) of an empty set returns 0 (count is always non-NULL).

The treatment matches ISO SQL and is documented in NULL semantics.

DISTINCT inside aggregates

Most aggregates accept a DISTINCT modifier that deduplicates the input before aggregating. The conventional use is COUNT(DISTINCT col), but the modifier applies equally to SUM, AVG, MIN, MAX:

SELECT
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(DISTINCT amount)        AS sum_of_distinct_values
FROM bouquet_order;

SUM(DISTINCT) is rarely useful and is mostly a curiosity. COUNT(DISTINCT) is one of the most-used analytic tools in SQL and supports the conventional cardinality reports.

GROUP BY

GROUP BY collapses rows that share a column value (or a tuple of column values) into a single output row. Aggregates over the group are computed per group:

SELECT
    customer_id,
    COUNT(*)        AS order_count,
    SUM(amount)     AS lifetime_value
FROM bouquet_order
GROUP BY customer_id;

The result has one row per customer_id. GROUP BY may take several columns, in which case rows are grouped by the tuple:

SELECT
    strftime('%Y-%m', placed_at) AS month,
    customer_id,
    COUNT(*) AS orders_in_month
FROM bouquet_order
GROUP BY month, customer_id;

The GROUP BY clause can reference column aliases declared in the SELECT clause (an SQLite convenience; ISO SQL does not permit this) or use the column’s positional number:

SELECT strftime('%Y-%m', placed_at) AS month, COUNT(*) AS n
FROM bouquet_order
GROUP BY month;          -- alias

SELECT strftime('%Y-%m', placed_at), COUNT(*)
FROM bouquet_order
GROUP BY 1;              -- positional

Bare columns

A query that mixes aggregates and bare columns is, in ISO SQL, an error: every non-aggregated column must appear in GROUP BY. SQLite is permissive in this regard and accepts queries like:

SELECT customer_id, name, COUNT(*) FROM bouquet_order JOIN customer ON …
GROUP BY customer_id;

Here name is not in the GROUP BY and is not an aggregate. SQLite returns an arbitrary name from one of the rows in each group. If the relationship between customer_id and name is one-to-one (a foreign-key relationship in which customer_id determines name), the result is the expected one; if not, a single, unspecified value is returned. The recommendation in production code is to enumerate non-aggregate columns in GROUP BY even when SQLite would accept the more permissive form, so that the query is portable across engines and so that an unintended ambiguity is rejected at parse time.

HAVING

HAVING filters groups by aggregate values. It runs after GROUP BY and is the only place aggregates may appear in a filter expression:

SELECT
    customer_id,
    COUNT(*)    AS order_count,
    SUM(amount) AS lifetime_value
FROM bouquet_order
GROUP BY customer_id
HAVING SUM(amount) > 1000;

WHERE cannot use aggregates because it runs before GROUP BY; HAVING exists for the use case of filtering on aggregates.

The conventional rule: WHERE filters individual rows, HAVING filters groups. A query that uses HAVING for a non-aggregate condition is mis-shaped and would run faster as a WHERE:

-- Mis-shaped: HAVING used for a non-aggregate filter
SELECT customer_id, SUM(amount) FROM bouquet_order
GROUP BY customer_id
HAVING customer_id = 42;

-- Better: WHERE applies before grouping, smaller intermediate result
SELECT customer_id, SUM(amount) FROM bouquet_order
WHERE customer_id = 42
GROUP BY customer_id;

FILTER

The FILTER (WHERE …) clause restricts an aggregate to rows that match a predicate. It is more general than CASE … END aggregates and is supported by SQLite from version 3.30:

SELECT
    customer_id,
    COUNT(*)                                                AS total_orders,
    COUNT(*) FILTER (WHERE amount > 100)                    AS large_orders,
    SUM(amount)                                             AS revenue,
    SUM(amount) FILTER (WHERE strftime('%Y', placed_at) = '2025') AS revenue_2025
FROM bouquet_order
GROUP BY customer_id;

FILTER is the conventional alternative to the older SUM(CASE WHEN cond THEN col ELSE 0 END) pattern. The newer form is more readable and frequently allows the planner to optimise more effectively.

CASE inside aggregates

Before FILTER was widely available, conditional aggregation was expressed with CASE:

SELECT
    SUM(CASE WHEN amount > 100 THEN 1 ELSE 0 END)            AS large_count,
    SUM(CASE WHEN strftime('%Y', placed_at) = '2025' THEN amount ELSE 0 END) AS revenue_2025
FROM bouquet_order;

The pattern still works and is portable across SQL engines that lack FILTER. The recommendation in modern SQLite is FILTER; the CASE form is documented here for legibility when reading older code.

DISTINCT vs GROUP BY

SELECT DISTINCT returns each unique projection. A GROUP BY over the same columns, with no aggregate, has the same effect:

SELECT DISTINCT category FROM bouquet;
SELECT category FROM bouquet GROUP BY category;     -- equivalent

The two forms are semantically equivalent and the planner often produces identical plans. DISTINCT is more readable for the simple case; GROUP BY is preferred when an aggregate is added or when the query already groups for other reasons.

ROLLUP, CUBE, GROUPING SETS

SQLite does not implement the SQL standard’s ROLLUP, CUBE, or GROUPING SETS. The conventional substitute for sub-totals is to run separate queries (one per grouping level) and combine with UNION ALL:

SELECT category, SUM(amount) AS revenue FROM bouquet_order JOIN bouquet USING (bouquet_id)
GROUP BY category
UNION ALL
SELECT 'TOTAL', SUM(amount) FROM bouquet_order;

For more complex grouping-set patterns, the recommendation is to compute the aggregates in the application layer or to use a CTE that produces the desired structure.

Worked patterns

Cardinality

How many distinct values:

SELECT COUNT(DISTINCT customer_id) AS distinct_customers FROM bouquet_order;

Top N per group

The top three orders per customer (using a window function; covered in Window functions):

WITH ranked AS (
    SELECT
        customer_id,
        amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rk
    FROM bouquet_order
)
SELECT customer_id, amount FROM ranked WHERE rk <= 3;

Histogram

Count rows per bucket:

SELECT
    CASE
        WHEN price <  10 THEN '0-10'
        WHEN price <  30 THEN '10-30'
        WHEN price < 100 THEN '30-100'
        ELSE                  '100+'
    END AS price_band,
    COUNT(*) AS n
FROM bouquet
GROUP BY price_band
ORDER BY MIN(price);

The ORDER BY MIN(price) sorts the bands by the minimum price they contain, which puts them in numeric order regardless of the alphabetic order of the band labels.

Pivot

A pivot — turning rows into columns — is expressed with conditional aggregates. SQLite has no PIVOT keyword.

SELECT
    customer_id,
    SUM(CASE WHEN strftime('%Y', placed_at) = '2024' THEN amount ELSE 0 END) AS revenue_2024,
    SUM(CASE WHEN strftime('%Y', placed_at) = '2025' THEN amount ELSE 0 END) AS revenue_2025,
    SUM(CASE WHEN strftime('%Y', placed_at) = '2026' THEN amount ELSE 0 END) AS revenue_2026
FROM bouquet_order
GROUP BY customer_id;

The FILTER form is equivalent and more readable:

SELECT
    customer_id,
    SUM(amount) FILTER (WHERE strftime('%Y', placed_at) = '2024') AS revenue_2024,
    SUM(amount) FILTER (WHERE strftime('%Y', placed_at) = '2025') AS revenue_2025,
    SUM(amount) FILTER (WHERE strftime('%Y', placed_at) = '2026') AS revenue_2026
FROM bouquet_order
GROUP BY customer_id;

Running total

A running total — cumulative sum — is a window function rather than an aggregate; it is documented in Window functions.

Performance

Aggregations over a large table benefit substantially from indexes that match the GROUP BY and WHERE clauses. The index permits the engine to compute the groups in index order, avoiding a sort step:

CREATE INDEX ix_order_customer ON bouquet_order (customer_id);

SELECT customer_id, COUNT(*) FROM bouquet_order GROUP BY customer_id;
-- Plan: USE COVERING INDEX ix_order_customer  (no sort needed)

EXPLAIN QUERY PLAN reveals whether a sort step (USE TEMP B-TREE FOR GROUP BY) is required; an index that covers the grouping columns eliminates it. For a query that runs frequently and aggregates a large table, the right index can be the difference between an interactive response and a multi-second wait.