¶Aggregation

This page covers aggregate SQL syntax. For how large aggregations execute (streaming, parallel, index-aware), see Analytics (OLAP).

¶Aggregate functions

COUNT, SUM, AVG, MIN, MAX, each with an optional DISTINCT:

SELECT COUNT(*), SUM(amount), AVG(amount), MIN(amount), MAX(amount) FROM sales;
SELECT COUNT(DISTINCT region) FROM sales;

• COUNT(*) over zero rows returns 0.
• SUM/MIN/MAX preserve the argument's type where meaningful (e.g. SUM over DECIMAL is exact); AVG returns a float.

Also available: STDDEV/STDDEV_POP/STDDEV_SAMP, VARIANCE/VAR_POP/ VAR_SAMP, the bitwise aggregates BIT_OR/BIT_AND/BIT_XOR, and percentiles:

-- percentile_cont (linear interpolation); p in 0..1
SELECT service,
       MEDIAN(latency_ms)             AS p50,
       PERCENTILE(latency_ms, 0.95)   AS p95,   -- QUANTILE is an alias
       PERCENTILE(latency_ms, 0.99)   AS p99
FROM requests
GROUP BY service;

PERCENTILE(col, p) / QUANTILE(col, p) compute an exact percentile of the group's numeric values (MEDIAN(col) = PERCENTILE(col, 0.5)); an empty group returns NULL. Exact computation buffers the group's values, so memory scales with group size.

¶GROUP BY

SELECT region, COUNT(*), SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY total DESC;

GROUP BY accepts one or more columns or expressions. Grouping by an expression is how you bucket data — most importantly by time:

-- Requests, errors and latency percentiles per minute (an observability query):
SELECT DATE_FORMAT(ts, '%Y-%m-%d %H:%i:00') AS minute,
       COUNT(*)                     AS requests,
       SUM(status >= 500)           AS errors,
       PERCENTILE(latency_ms, 0.95) AS p95
FROM logs
GROUP BY DATE_FORMAT(ts, '%Y-%m-%d %H:%i:00')
ORDER BY minute;

SELECT status DIV 100 AS class, COUNT(*) FROM logs GROUP BY status DIV 100;

Projecting the same expression returns each group's value. Aggregation works over joined result sets too.

¶HAVING

Filter groups after aggregation. HAVING may reference aggregates or a projection alias:

SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING SUM(amount) > 1000        -- or: HAVING total > 1000
ORDER BY total DESC;

SELECT region, COUNT(*) AS n FROM sales GROUP BY region HAVING COUNT(*) >= 3;

HAVING references must appear in the SELECT list (as an aggregate expression or an alias) or be a grouped column.

¶GROUP_CONCAT

SELECT region, GROUP_CONCAT(name) AS names FROM stores GROUP BY region;
SELECT region, GROUP_CONCAT(DISTINCT name SEPARATOR '; ') FROM stores GROUP BY region;

GROUP_CONCAT concatenates a group's values (default separator ,), and supports DISTINCT and a custom SEPARATOR. Ordering within the group follows row order (an inner ORDER BY is not yet applied).

¶FACET — faceted search counts

FACET(col) returns a JSON object mapping each distinct value of col to its count over the matched rows — the counts side of a faceted search. Because it is an ordinary aggregate, every facet plus the hit count is computed in a single pass, and it composes with WHERE, full-text MATCH ... AGAINST, vector filters, and GROUP BY:

-- All facets and the total for one search, in one scan:
SELECT FACET(category) AS categories,
       FACET(brand, 10) AS brands,   -- top-10 brands by count
       COUNT(*)         AS total
FROM docs
WHERE MATCH(title, body) AGAINST('rust database');
-- categories -> {"db": 4, "sys": 1, "web": 1}

Values are ordered by count (descending), then value (ascending); the optional second argument caps the result to the top-N values. NULLs are not counted. The page of matching rows comes from the normal SELECT ... WHERE ... ORDER BY ... LIMIT query; FACET answers the counts efficiently alongside it.

¶Window functions

Window functions compute a value per row over a partition, without collapsing rows:

SELECT id, region, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn,
       RANK()       OVER (ORDER BY amount)                          AS rk,
       SUM(amount)  OVER (PARTITION BY region ORDER BY id)         AS running,
       SUM(amount)  OVER (PARTITION BY region)                     AS region_total,
       LAG(amount)  OVER (ORDER BY id)                             AS prev
FROM sales;

Supported: ROW_NUMBER, RANK, DENSE_RANK, SUM/COUNT/AVG/MIN/MAX OVER (...), and LAG/LEAD. With ORDER BY in the window, aggregates are running (cumulative, peers share a value); without it they cover the whole partition.

¶Frames

Explicit ROWS frames (physical row offsets) are supported:

-- 3-row moving sum
SUM(v) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- centered average
AVG(v) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
-- suffix sum
SUM(v) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

RANGE supports numeric value offsets with one numeric ORDER BY expression, including ascending and descending order. GROUPS supports offsets measured in peer groups and may use multiple ORDER BY expressions. Temporal RANGE offsets are not yet supported.

¶The OLAP engine

Large aggregations run through a dedicated analytical path:

• Streaming — the table is scanned in batches; only per-group state is retained, so memory is proportional to the number of groups, not the table size. Aggregating a billion-row table does not exhaust memory.
• Parallel — batches are aggregated across worker threads and merged, using all cores.
• Index-aware — an aggregation with a selective, indexed WHERE (equality or range) reads only the matching rows via the index instead of scanning.

-- reads only matching rows via the index, then aggregates
SELECT SUM(amount) FROM sales WHERE region = 'north';

-- full parallel streaming aggregation
SELECT region, COUNT(*) FROM sales GROUP BY region;