Examples

Produce a single row containing the sum of the amount column:

SELECT sum(amount)
FROM sales;

Produce one row per unique region, containing the sum of amount for each group:

SELECT region, sum(amount)
FROM sales
GROUP BY region;

Return only the regions that have a sum of amount higher than 100:

SELECT region
FROM sales
GROUP BY region
HAVING sum(amount) > 100;

Return the number of unique values in the region column:

SELECT count(DISTINCT region)
FROM sales;

Return two values, the total sum of amount and the sum of amount minus columns where the region is north using the FILTER clause:

SELECT sum(amount), sum(amount) FILTER (region != 'north')
FROM sales;

Returns a list of all regions in order of the amount column:

SELECT list(region ORDER BY amount DESC)
FROM sales;

Returns the amount of the first sale using the first() aggregate function:

SELECT first(amount ORDER BY date ASC)
FROM sales;

Syntax

Aggregates are functions that combine multiple rows into a single value. Aggregates are different from scalar functions and window functions because they change the cardinality of the result. As such, aggregates can only be used in the SELECT and HAVING clauses of a SQL query.

DISTINCT Clause in Aggregate Functions

When the DISTINCT clause is provided, only distinct values are considered in the computation of the aggregate. This is typically used in combination with the count aggregate to get the number of distinct elements; but it can be used together with any aggregate function in the system.

ORDER BY Clause in Aggregate Functions

An ORDER BY clause can be provided after the last argument of the function call. Note the lack of the comma separator before the clause.

SELECT ⟨aggregate_function⟩(⟨arg⟩, ⟨sep⟩ ORDER BY ⟨ordering_criteria⟩);

This clause ensures that the values being aggregated are sorted before applying the function. Most aggregate functions are order-insensitive, and for them this clause is parsed and discarded. However, there are some order-sensitive aggregates that can have non-deterministic results without ordering, e.g., first, last, list and string_agg / group_concat / listagg. These can be made deterministic by ordering the arguments.

For example:

CREATE TABLE tbl AS
    SELECT s FROM range(1, 4) r(s);

SELECT string_agg(s, ', ' ORDER BY s DESC) AS countdown
FROM tbl;

General Aggregate Functions

The table below shows the available general aggregate functions.

any_value(arg)

arbitrary(arg)

arg_max(arg, val)

arg_min(arg, val)

array_agg(arg)

avg(arg)

bit_and(arg)

bit_or(arg)

bit_xor(arg)

bitstring_agg(arg)

bool_and(arg)

bool_or(arg)

count(arg)

favg(arg)

first(arg)

fsum(arg)

geomean(arg)

histogram(arg)

last(arg)

list(arg)

max(arg)

max_by(arg, val)

min(arg)

min_by(arg, val)

product(arg)

string_agg(arg, sep)

sum(arg)

sum_no_overflow(arg)

Approximate Aggregates

The table below shows the available approximate aggregate functions.

Statistical Aggregates

The table below shows the available statistical aggregate functions. They all ignore NULL values (in the case of a single input column x), or pairs where either input is NULL (in the case of two input columns y and x).

corr(y, x)

covar_pop(y, x)

covar_samp(y, x)

entropy(x)

kurtosis_pop(x)

kurtosis(x)

mad(x)

median(x)

mode(x)

quantile_cont(x, pos)

quantile_disc(x, pos)

regr_avgx(y, x)

regr_avgy(y, x)

regr_count(y, x)

regr_intercept(y, x)

regr_r2(y, x)

regr_slope(y, x)

regr_sxx(y, x)

regr_sxy(y, x)

regr_syy(y, x)

skewness(x)

stddev_pop(x)

stddev_samp(x)

var_pop(x)

var_samp(x)

Ordered Set Aggregate Functions

The table below shows the available “ordered set” aggregate functions. These functions are specified using the WITHIN GROUP (ORDER BY sort_expression) syntax, and they are converted to an equivalent aggregate function that takes the ordering expression as the first argument.

Miscellaneous Aggregate Functions