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.
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.
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;
Finds the row with the maximum val and calculates the arg expression at that row. Rows where the value of the arg or val expression is NULL are ignored. This function is affected by ordering.
The generalized case of arg_max for n values: returns a LIST containing the arg expressions for the top n rows ordered by val descending. This function is affected by ordering.
Finds the row with the maximum val and calculates the arg expression at that row. Rows where the val expression evaluates to NULL are ignored. This function is affected by ordering.
Finds the row with the minimum val and calculates the arg expression at that row. Rows where the value of the arg or val expression is NULL are ignored. This function is affected by ordering.
Finds the row with the minimum val and calculates the arg expression at that row. Rows where the val expression evaluates to NULL are ignored. This function is affected by ordering.
Finds the row with the maximum val and calculates the arg expression at that row. Rows where the value of the arg or val expression is NULL are ignored. This function is affected by ordering.
The generalized case of arg_max for n values: returns a LIST containing the arg expressions for the top n rows ordered by val descending. This function is affected by ordering.
Finds the row with the maximum val and calculates the arg expression at that row. Rows where the val expression evaluates to NULL are ignored. This function is affected by ordering.
Finds the row with the minimum val and calculates the arg expression at that row. Rows where the value of the arg or val expression is NULL are ignored. This function is affected by ordering.
The generalized case of arg_min for n values: returns a LIST containing the arg expressions for the top n rows ordered by val descending. This function is affected by ordering.
Finds the row with the minimum val and calculates the arg expression at that row. Rows where the val expression evaluates to NULL are ignored. This function is affected by ordering.
All general aggregate functions except for list and first (and their aliases array_agg and arbitrary, respectively) ignore NULLs. To exclude NULLs from list, you can use a FILTER clause. To ignore NULLs from first, you can use the any_value aggregate.
All general aggregate functions except count return NULL on empty groups and groups without non-NULL inputs. In particular, list does not return an empty list, sum does not return zero, and string_agg does not return an empty string in this case.
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).
The interpolated pos-quantile of x for 0 <= pos <= 1, i.e., orders the values of x and returns the pos * (n_nonnull_values - 1)th (zero-indexed) element (or an interpolation between the adjacent elements if the index is not an integer). If pos is a LIST of FLOATs, then the result is a LIST of the corresponding interpolated quantiles.
The discrete pos-quantile of x for 0 <= pos <= 1, i.e., orders the values of x and returns the greatest(ceil(pos * n_nonnull_values) - 1, 0)th (zero-indexed) element. If pos is a LIST of FLOATs, then the result is a LIST of the corresponding discrete quantiles.
The squared Pearson correlation coefficient between y and x. Also: The coefficient of determination in a linear regression, where x is the independent variable and y is the dependent variable.
The population variance, which includes Bessel's bias correction, of the independent variable for non-NULL pairs, where x is the independent variable and y is the dependent variable.
The population variance, which includes Bessel's bias correction, of the dependent variable for non-NULL pairs , where x is the independent variable and y is the dependent variable.
The interpolated pos-quantile of x for 0 <= pos <= 1, i.e., orders the values of x and returns the pos * (n_nonnull_values - 1)th (zero-indexed) element (or an interpolation between the adjacent elements if the index is not an integer). If pos is a LIST of FLOATs, then the result is a LIST of the corresponding interpolated quantiles.
The discrete pos-quantile of x for 0 <= pos <= 1, i.e., orders the values of x and returns the greatest(ceil(pos * n_nonnull_values) - 1, 0)th (zero-indexed) element. If pos is a LIST of FLOATs, then the result is a LIST of the corresponding discrete quantiles.
The squared Pearson correlation coefficient between y and x. Also: The coefficient of determination in a linear regression, where x is the independent variable and y is the dependent variable.
The population variance, which includes Bessel's bias correction, of the independent variable for non-NULL pairs, where x is the independent variable and y is the dependent variable.
The population variance, which includes Bessel's bias correction, of the dependent variable for non-NULL pairs, where x is the independent variable and y is the dependent variable.
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.
Function
Equivalent
mode() WITHIN GROUP (ORDER BY column [(ASC|DESC)])
mode(column ORDER BY column [(ASC|DESC)])
percentile_cont(fraction) WITHIN GROUP (ORDER BY column [(ASC|DESC)])
quantile_cont(column, fraction ORDER BY column [(ASC|DESC)])
percentile_cont(fractions) WITHIN GROUP (ORDER BY column [(ASC|DESC)])
quantile_cont(column, fractions ORDER BY column [(ASC|DESC)])
percentile_disc(fraction) WITHIN GROUP (ORDER BY column [(ASC|DESC)])
quantile_disc(column, fraction ORDER BY column [(ASC|DESC)])
percentile_disc(fractions) WITHIN GROUP (ORDER BY column [(ASC|DESC)])
quantile_disc(column, fractions ORDER BY column [(ASC|DESC)])
For queries with GROUP BY and either ROLLUP or GROUPING SETS: Returns an integer identifying which of the argument expressions where used to group on to create the current supper-aggregate row.
