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.
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.
Calculates the sum value for all tuples in arg without overflow checks. Unlike sum, which works on floating-point values, sum_no_overflow only accepts INTEGER and DECIMAL values.
Calculates the sum value for all tuples in arg without overflow checks. Unlike sum, which works on floating-point values, sum_no_overflow only accepts INTEGER and DECIMAL values.
Returns the middle value of the set. NULL values are ignored. For even value counts, quantitative values are averaged and ordinal values return the lower value.
Returns 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.
Returns the discrete pos-quantile of x for 0 <= pos <= 1, i.e., orders the values of x and returns the floor(pos * (n_nonnull_values - 1))th (zero-indexed) element. If pos is a LIST of FLOATs, then the result is a LIST of the corresponding discrete quantiles.
Returns the middle value of the set. NULL values are ignored. For even value counts, quantitative values are averaged and ordinal values return the lower value.
Returns 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.
Returns the discrete pos-quantile of x for 0 <= pos <= 1, i.e., orders the values of x and returns the floor(pos * (n_nonnull_values - 1))th (zero-indexed) element. If pos is a LIST of FLOATs, then the result is a LIST of the corresponding discrete quantiles.
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.
