Aggregate functions
General, statistical, approximate, regression and ordered aggregates.
Aggregate functions reduce a group of rows to one result. Use GROUP BY to define groups; without it, the complete input is one group.
SELECT region,
count(*) AS orders,
sum(net_amount) AS revenue,
avg(net_amount) AS average_order
FROM sales.orders
GROUP BY region;Modifiers
DISTINCTremoves duplicate inputs for the aggregate.FILTER (WHERE condition)includes only matching rows.ORDER BYinside an aggregate controls order-sensitive functions.
SELECT account_id,
count(*) FILTER (WHERE status = 'failed') AS failures,
string_agg(event_type, ', ' ORDER BY occurred_at) AS event_path
FROM account_events
GROUP BY account_id;General aggregates
| Functions | Result |
|---|---|
count, countif | row or conditional count |
sum, fsum, avg, favg, product, weighted_avg | numeric reduction |
min, max, arg_min, arg_max, arg_min_null, arg_max_null, min_by, max_by | extremes and associated values |
first, last, any_value, arbitrary | one value from the group |
list, array_agg, string_agg, group_concat | ordered collection |
bool_and, bool_or, every | boolean reduction |
bit_and, bit_or, bit_xor, bitstring_agg | bit reduction |
histogram, histogram_exact, histogram_values | frequency summaries |
geometric_mean | geometric mean for positive numeric input |
first, last, list, string_agg and tie-sensitive arg_* calls are order-dependent. Add an aggregate ORDER BY when repeatable order matters.
min(value, n), max(value, n), arg_min(arg, value, n) and arg_max(arg, value, n) return bounded top-N collections. n must be a supported positive constant and is subject to aggregate-state limits.
Statistical aggregates
| Family | Functions |
|---|---|
| spread | min, max, median, mad, quantile_cont, quantile_disc |
| variance | var_pop, var_samp, variance, stddev_pop, stddev_samp, stddev, sem |
| shape | skewness, kurtosis, kurtosis_pop, entropy, mode |
| covariance | covar_pop, covar_samp, corr |
| regression | regr_avgx, regr_avgy, regr_count, regr_intercept, regr_r2, regr_slope, regr_sxx, regr_sxy, regr_syy |
Sample variants use an n - 1 denominator and return NULL when the sample is too small. Population variants use n.
Approximate aggregates
| Function | Use |
|---|---|
approx_count_distinct(value) | HyperLogLog distinct estimate |
approx_quantile(value, position) | T-Digest approximate quantile |
approx_top_k(value, k) | approximate most-frequent values |
reservoir_quantile(value, position [, sample_size]) | reservoir-sampled quantile |
Approximate functions trade exactness for bounded memory and speed. Do not use them where audit, billing or correctness policy requires exact results.
NULL and empty groups
Most aggregates ignore NULL inputs. count(*) counts rows; count(expression) counts non-NULL values. Except count, aggregates over an empty group return NULL. Use coalesce only when zero or an empty collection is the correct domain value.