Aggregate Functions

Examples

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

SQL

SELECT sum(amount)FROM sales;

 sum
-----
 170

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

SQL

SELECT region, sum(amount)FROM salesGROUP BY region;

 region | sum
--------+-----
 east   | 110
 north  |  40
 south  |  20

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

SQL

SELECT regionFROM salesGROUP BY regionHAVING sum(amount) > 100;

 region
--------
 east

Return the number of unique values in the region column:

SQL

SELECT count(DISTINCT region)FROM sales;

 count
-------
     3

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

SQL

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

 sum | sum
-----+-----
 170 | 130

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

SQL

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

 list
--------------------------
 {east,north,south,north}

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

SQL

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

 first
-------
    10

Syntax

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. There are some aggregates that are insensitive to duplicate values (e.g., min and max) and for them this clause is parsed and ignored.

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.

SQL

SELECT string_agg(region, ', ' ORDER BY amount DESC) AS string_aggFROM sales;

 string_agg
---------------------------
 east, north, south, north

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:

SQL

CREATE TABLE tbl AS    SELECT s FROM range(1, 4) r(s);
SELECT string_agg(s, ', ' ORDER BY s DESC) AS countdownFROM tbl;

 countdown
-----------
 3, 2, 1

Handling NULL Values

All general aggregate functions ignore NULLs, except for list (array_agg), first (arbitrary) and last. 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. 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.

General Aggregate Functions

The examples in this section run against the following table:

SQL

CREATE TABLE agg_demo (a INTEGER, b INTEGER, s VARCHAR, w DOUBLE, bl BOOLEAN);
INSERT INTO agg_demo VALUES    (10, 1, 'a', 1.0, true),    (20, 2, 'b', 2.0, false),    (30, 3, 'c', 3.0, true),    (40, 4, 'd', 4.0, true);

The table below shows the available general aggregate functions.

Function	Description
any_value(arg)	Returns the first non-null value from arg. This function is affected by ordering.
arbitrary(arg)	Returns the first value — NULL or not — from arg. Cheaper than any_value when any row will do and NULLs are acceptable. This function is affected by ordering.
arg_max(arg, val)	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.
arg_max(arg, val, n)	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.
arg_max_null(arg, val)	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.
arg_max_nulls_last(arg, val)	Like arg_max_null, but rows whose val expression is NULL are also kept, and are ordered after every row with a value. Takes an optional third argument n for the top n rows.
arg_min(arg, val)	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.
arg_min(arg, val, n)	Returns a LIST containing the arg expressions for the "bottom" n rows ordered by val ascending. This function is affected by ordering.
arg_min_null(arg, val)	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.
arg_min_nulls_last(arg, val)	Like arg_min_null, but rows whose val expression is NULL are also kept, and are ordered after every row with a value. Takes an optional third argument n for the bottom n rows.
avg(arg)	Calculates the average of all non-null values in arg. This function is affected by ordering.
bit_and(arg)	Returns the bitwise AND of all bits in a given expression.
bit_or(arg)	Returns the bitwise OR of all bits in a given expression.
bit_xor(arg)	Returns the bitwise XOR of all bits in a given expression.
bitstring_agg(arg)	Returns a bitstring whose length corresponds to the range of the non-null (integer) values, with bits set at the location of each (distinct) value.
bool_and(arg)	Returns true if every input value is true, otherwise false.
bool_or(arg)	Returns true if any input value is true, otherwise false.
count()	Returns the number of rows.
count(arg)	Returns the number of rows where arg is not NULL.
countif(arg)	Returns the number of rows where arg is true.
decimal_average(arg)	Averages a DECIMAL column with exact integer arithmetic and banker's rounding, returning a DECIMAL of the same width and scale as the input. Use it instead of avg when the result must stay exact.
every(arg)	Returns TRUE when every input value is TRUE. The SQL-standard spelling of bool_and.
favg(arg)	Calculates the average using a more accurate floating point summation (Kahan Sum). This function is affected by ordering.
first(arg)	Returns the first value (null or non-null) from arg. This function is affected by ordering.
fsum(arg)	Calculates the sum using a more accurate floating point summation (Kahan Sum). This function is affected by ordering.
geometric_mean(arg)	Calculates the geometric mean of all non-null values in arg. This function is affected by ordering.
histogram(arg)	Returns a MAP of key-value pairs representing buckets and counts.
histogram(arg, boundaries)	Returns a MAP of key-value pairs representing the provided upper boundaries and counts of elements in the corresponding bins (left-open and right-closed partitions) of the datatype. A boundary at the largest value of the datatype is automatically added when elements larger than all provided boundaries appear, see is_histogram_other_bin. Boundaries may be provided, e.g., via equi_width_bins.
histogram_exact(arg, elements)	Returns a MAP of key-value pairs representing the requested elements and their counts. A catch-all element specific to the data-type is automatically added to count other elements when they appear, see is_histogram_other_bin.
histogram_values(source, boundaries)	Returns the upper boundaries of the bins and their counts.
kahan_sum(arg)	Sums a floating-point column with Kahan compensated summation, which keeps far more precision than sum when the values differ widely in magnitude.
last(arg)	Returns the last value of a column. This function is affected by ordering.
list(arg)	Returns a LIST containing all the values of a column. This function is affected by ordering.
max(arg)	Returns the maximum value present in arg. This function is unaffected by distinctness.
max(arg, n)	Returns a LIST containing the arg values for the "top" n rows ordered by arg descending.
min(arg)	Returns the minimum value present in arg. This function is unaffected by distinctness.
min(arg, n)	Returns a LIST containing the arg values for the "bottom" n rows ordered by arg ascending.
product(arg)	Calculates the product of all non-null values in arg. This function is affected by ordering.
string_agg(arg)	Concatenates the column string values with a comma separator (,). This function is affected by ordering.
string_agg(arg, sep)	Concatenates the column string values with a separator. This function is affected by ordering.
sum(arg)	Calculates the sum of all non-null values in arg / counts true values when arg is boolean. The floating-point versions of this function are affected by ordering.
weighted_avg(arg, weight)	Calculates the weighted average of all non-null values in arg, where each value is scaled by its corresponding weight. If weight is NULL, the corresponding arg value will be skipped. This function is affected by ordering.

any_value(arg)

Returns the first non-NULL value from arg. This function is affected by ordering.

SQL

SELECT any_value(a ORDER BY a) AS any_value FROM agg_demo;

 any_value
-----------
        10

arg_max(arg, val)

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. Aliases: argmax(arg, val), max_by(arg, val).

SQL

SELECT arg_max(a, b) AS arg_max FROM agg_demo;

 arg_max
---------
      40

arg_max(arg, val, n)

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. Aliases: argmax(arg, val, n), max_by(arg, val, n).

SQL

SELECT arg_max(a, b, 2) AS arg_max FROM agg_demo;

 arg_max
---------
 {40,30}

arg_max_null(arg, val)

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.

SQL

SELECT arg_max_null(a, b) AS arg_max_null FROM agg_demo;

 arg_max_null
--------------
           40

arg_min(arg, val)

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. Aliases: argmin(arg, val), min_by(arg, val).

SQL

SELECT arg_min(a, b) AS arg_min FROM agg_demo;

 arg_min
---------
      10

arg_min(arg, val, n)

The generalized case of arg_min for n values: returns a LIST containing the arg expressions for the bottom n rows ordered by val ascending. This function is affected by ordering. Aliases: argmin(arg, val, n), min_by(arg, val, n).

SQL

SELECT arg_min(a, b, 2) AS arg_min FROM agg_demo;

 arg_min
---------
 {10,20}

arg_min_null(arg, val)

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.

SQL

SELECT arg_min_null(a, b) AS arg_min_null FROM agg_demo;

 arg_min_null
--------------
           10

avg(arg)

Calculates the average of all non-null values in arg. This function is affected by ordering. Alias: mean.

SQL

SELECT avg(a) AS avg FROM agg_demo;

 avg
-----
  25

bit_and(arg)

Returns the bitwise AND of all bits in a given expression.

SQL

SELECT bit_and(a) AS bit_and FROM agg_demo;

 bit_and
---------
       0

bit_or(arg)

Returns the bitwise OR of all bits in a given expression.

SQL

SELECT bit_or(a) AS bit_or FROM agg_demo;

 bit_or
--------
     62

bit_xor(arg)

Returns the bitwise XOR of all bits in a given expression.

SQL

SELECT bit_xor(a) AS bit_xor FROM agg_demo;

 bit_xor
---------
      40

bitstring_agg(arg)

Returns a bitstring whose length corresponds to the range of the non-null (integer) values, with bits set at the location of each (distinct) value.

SQL

SELECT bitstring_agg(a) AS bitstring_agg FROM agg_demo;

 bitstring_agg
---------------------------------
 1000000000100000000010000000001

bool_and(arg)

Returns true if every input value is true, otherwise false.

SQL

SELECT bool_and(bl) AS bool_and FROM agg_demo;

 bool_and
----------
 f

bool_or(arg)

Returns true if any input value is true, otherwise false.

SQL

SELECT bool_or(bl) AS bool_or FROM agg_demo;

 bool_or
---------
 t

count()

Returns the number of rows. Alias: count(*).

SQL

SELECT count() AS count FROM agg_demo;

 count
-------
     4

count(arg)

Returns the number of rows where arg is not NULL.

SQL

SELECT count(a) AS count FROM agg_demo;

 count
-------
     4

countif(arg)

Returns the number of rows where arg is true.

SQL

SELECT countif(bl) AS countif FROM agg_demo;

 countif
---------
       3

favg(arg)

Calculates the average using a more accurate floating point summation (Kahan Sum). This function is affected by ordering.

SQL

SELECT favg(a) AS favg FROM agg_demo;

 favg
------
   25

first(arg)

Returns the first value (null or non-null) from arg. This function is affected by ordering. Alias: arbitrary(A).

SQL

SELECT first(a ORDER BY a) AS first FROM agg_demo;

 first
-------
    10

fsum(arg)

Calculates the sum using a more accurate floating point summation (Kahan Sum). This function is affected by ordering. Aliases: sumkahan, kahan_sum.

SQL

SELECT fsum(a) AS fsum FROM agg_demo;

 fsum
------
  100

geometric_mean(arg)

Calculates the geometric mean of all non-null values in arg. This function is affected by ordering. Alias: geomean(A).

SQL

SELECT geometric_mean(a) AS geometric_mean FROM agg_demo;

 geometric_mean
--------------------
 22.133638394006436

histogram(arg)

Returns a MAP of key-value pairs representing buckets and counts.

SQL

SELECT histogram(a) AS histogram FROM agg_demo;

 histogram
---------------------------------------
 {"(10,1)","(20,1)","(30,1)","(40,1)"}

histogram(arg, boundaries)

Returns a MAP of key-value pairs representing the provided upper boundaries and counts of elements in the corresponding bins (left-open and right-closed partitions) of the datatype. A boundary at the largest value of the datatype is automatically added when elements larger than all provided boundaries appear, see is_histogram_other_bin. Boundaries may be provided, e.g., via equi_width_bins.

SQL

SELECT histogram(a, [0, 1, 10]) AS histogram FROM agg_demo;

 histogram
---------------------------------------------
 {"(0,0)","(1,0)","(10,1)","(2147483647,3)"}

histogram_exact(arg, elements)

Returns a MAP of key-value pairs representing the requested elements and their counts. A catch-all element specific to the data-type is automatically added to count other elements when they appear, see is_histogram_other_bin.

SQL

SELECT histogram_exact(s, ['a', 'b', 'c']) AS histogram_exact FROM agg_demo;

 histogram_exact
----------------------------------------
 {"(a,1)","(b,1)","(c,1)","(\\"\\",1)"}

histogram_values(source, col_name, technique, bin_count)

Description	Returns the upper boundaries of the bins and their counts.
Example	histogram_values(integers, i, bin_count := 2)

Note: this function is not yet supported in SereneDB.

last(arg)

Returns the last value of a column. This function is affected by ordering.

SQL

SELECT last(a ORDER BY a) AS last FROM agg_demo;

 last
------
   40

list(arg)

Returns a LIST containing all the values of a column. This function is affected by ordering. Alias: array_agg.

SQL

SELECT list(a ORDER BY a) AS list FROM agg_demo;

 list
---------------
 {10,20,30,40}

max(arg)

Returns the maximum value present in arg. This function is unaffected by distinctness.

SQL

SELECT max(a) AS max FROM agg_demo;

 max
-----
  40

max(arg, n)

Returns a LIST containing the arg values for the "top" n rows ordered by arg descending.

SQL

SELECT max(a, 2) AS max FROM agg_demo;

 max
---------
 {40,30}

min(arg)

Returns the minimum value present in arg. This function is unaffected by distinctness.

SQL

SELECT min(a) AS min FROM agg_demo;

 min
-----
  10

min(arg, n)

Returns a LIST containing the arg values for the "bottom" n rows ordered by arg ascending.

SQL

SELECT min(a, 2) AS min FROM agg_demo;

 min
---------
 {10,20}

product(arg)

Calculates the product of all non-null values in arg. This function is affected by ordering.

SQL

SELECT product(a) AS product FROM agg_demo;

 product
---------
  240000

string_agg(arg)

Concatenates the column string values with a comma separator (,). This function is affected by ordering. Aliases: group_concat(arg), listagg(arg).

SQL

SELECT string_agg(s ORDER BY s) AS string_agg FROM agg_demo;

 string_agg
------------
 a,b,c,d

string_agg(arg, sep)

Concatenates the column string values with a separator. This function is affected by ordering. Aliases: group_concat(arg, sep), listagg(arg, sep).

SQL

SELECT string_agg(s, '-' ORDER BY s) AS string_agg FROM agg_demo;

 string_agg
------------
 a-b-c-d

sum(arg)

Calculates the sum of all non-null values in arg / counts true values when arg is boolean. The floating-point versions of this function are affected by ordering.

SQL

SELECT sum(a) AS sum FROM agg_demo;

 sum
-----
 100

weighted_avg(arg, weight)

Calculates the weighted average of all non-null values in arg, where each value is scaled by its corresponding weight. If weight is NULL, the value will be skipped. This function is affected by ordering. Alias: wavg(arg, weight).

SQL

SELECT weighted_avg(a, w) AS weighted_avg FROM agg_demo;

 weighted_avg
--------------
           30

Approximate Aggregates

The table below shows the available approximate aggregate functions.

Function	Description	Example
approx_count_distinct(x)	Calculates the approximate count of distinct elements using HyperLogLog.	approx_count_distinct(A)
approx_quantile(x, pos)	Calculates the approximate quantile using T-Digest.	approx_quantile(A, 0.5)
approx_top_k(arg, k)	Calculates a LIST of the k approximately most frequent values of arg using Filtered Space-Saving.
reservoir_quantile(x, quantile, sample_size = 8192)	Calculates the approximate quantile using reservoir sampling, the sample size is optional and uses 8192 as a default size.	reservoir_quantile(A, 0.5, 1024)

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).

Function	Description
corr(y, x)	The correlation coefficient.
covar_pop(y, x)	The population covariance, which does not include bias correction.
covar_samp(y, x)	The sample covariance, which includes Bessel's bias correction.
entropy(x)	The log-2 entropy of count input-values.
kurtosis_pop(x)	The excess kurtosis (Fisher’s definition) without bias correction.
kurtosis(x)	The excess kurtosis (Fisher's definition) with bias correction according to the sample size.
mad(x)	The median absolute deviation. Temporal types return a positive INTERVAL.
median(x)	The middle value of the set. For even value counts, quantitative values are averaged and ordinal values return the lower value.
mode(x)	The most frequent value. This function is affected by ordering.
quantile(x, pos)	The exact pos-quantile of x for 0 <= pos <= 1, an alias of quantile_disc. If pos is a LIST of FLOATs the result is a LIST of the corresponding quantiles. For an approximate answer over large inputs see approx_quantile.
quantile_cont(x, pos)	The interpolated pos-quantile of x for -1 <= pos <= 1. Returns the pos * (n_nonnull_values - 1)th (zero-indexed, in the specified order) value of x or an interpolation between the adjacent values if the index is not an integer. Values of pos between -1 and 0 correspond to counting backwards from 1. More precisely, quantile_cont(x, -y) = quantile_cont(x, 1 - y). Intuitively, arranges the values of x as equispaced points on a line, starting at 0 and ending at 1, and returns the (interpolated) value at pos. This is Type 7 in Hyndman & Fan (1996). If pos is a LIST of FLOATs, then the result is a LIST of the corresponding interpolated quantiles.
quantile_disc(x, pos)	The discrete pos-quantile of x for 0 <= pos <= 1. Returns the greatest(ceil(pos * n_nonnull_values) - 1, 0)th (zero-indexed, in the specified order) value of x. Intuitively, assigns to each value of x an equisized sub-interval (left-open and right-closed except for the initial interval) of the interval [0, 1], and picks the value of the sub-interval that contains pos. This is Type 1 in Hyndman & Fan (1996). If pos is a LIST of FLOATs, then the result is a LIST of the corresponding discrete quantiles.
regr_avgx(y, x)	The average of the independent variable for non-NULL pairs, where x is the independent variable and y is the dependent variable.
regr_avgy(y, x)	The average of the dependent variable for non-NULL pairs, where x is the independent variable and y is the dependent variable.
regr_count(y, x)	The number of non-NULL pairs.
regr_intercept(y, x)	The intercept of the univariate linear regression line, where x is the independent variable and y is the dependent variable.
regr_r2(y, x)	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.
regr_slope(y, x)	The slope of the linear regression line, where x is the independent variable and y is the dependent variable.
regr_sxx(y, x)	The sample 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.
regr_sxy(y, x)	The sample covariance, which includes Bessel's bias correction.
regr_syy(y, x)	The sample 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.
skewness(x)	The skewness.
sem(x)	The standard error of the mean.
stddev_pop(x)	The population standard deviation.
stddev_samp(x)	The sample standard deviation.
var_pop(x)	The population variance, which does not include bias correction.
var_samp(x)	The sample variance, which includes Bessel's bias correction.

corr(y, x)

Description	The correlation coefficient.
Formula	covar_pop(y, x) / (stddev_pop(x) * stddev_pop(y))

covar_pop(y, x)

Description	The population covariance, which does not include bias correction.
Formula	(sum(x*y) - sum(x) * sum(y) / regr_count(y, x)) / regr_count(y, x), covar_samp(y, x) * (1 - 1 / regr_count(y, x))

covar_samp(y, x)

Description	The sample covariance, which includes Bessel's bias correction.
Formula	(sum(x*y) - sum(x) * sum(y) / regr_count(y, x)) / (regr_count(y, x) - 1), covar_pop(y, x) / (1 - 1 / regr_count(y, x))
Alias(es)	regr_sxy(y, x)

entropy(x)

Description	The log-2 entropy of count input-values.
Formula	-

kurtosis_pop(x)

Description	The excess kurtosis (Fisher’s definition) without bias correction.
Formula	-

kurtosis(x)

Description	The excess kurtosis (Fisher's definition) with bias correction according to the sample size.
Formula	-

mad(x)

Description	The median absolute deviation. Temporal types return a positive INTERVAL.
Formula	median(abs(x - median(x)))

median(x)

Description	The middle value of the set. For even value counts, quantitative values are averaged and ordinal values return the lower value.
Formula	quantile_cont(x, 0.5)

mode(x)

Description	The most frequent value. This function is affected by ordering.
Formula	-

quantile_cont(x, pos)

Description

The interpolated pos-quantile of x for 0 <= pos <= 1. Returns the pos * (n_nonnull_values - 1)th (zero-indexed, in the specified order) value of x or an interpolation between the adjacent values if the index is not an integer. Intuitively, arranges the values of x as equispaced points on a line, starting at 0 and ending at 1, and returns the (interpolated) value at pos. This is Type 7 in Hyndman & Fan (1996). If pos is a LIST of FLOATs, then the result is a LIST of the corresponding interpolated quantiles.

Formula

-

quantile_disc(x, pos)

Description	The discrete pos-quantile of x for 0 <= pos <= 1. Returns the greatest(ceil(pos * n_nonnull_values) - 1, 0)th (zero-indexed, in the specified order) value of x. Intuitively, assigns to each value of x an equisized sub-interval (left-open and right-closed except for the initial interval) of the interval [0, 1], and picks the value of the sub-interval that contains pos. This is Type 1 in Hyndman & Fan (1996). If pos is a LIST of FLOATs, then the result is a LIST of the corresponding discrete quantiles.
Formula	-
Alias(es)	quantile

regr_avgx(y, x)

Description	The average of the independent variable for non-NULL pairs, where x is the independent variable and y is the dependent variable.
Formula	-

regr_avgy(y, x)

Description	The average of the dependent variable for non-NULL pairs, where x is the independent variable and y is the dependent variable.
Formula	-

regr_count(y, x)

Description	The number of non-NULL pairs.
Formula	-

regr_intercept(y, x)

Description	The intercept of the univariate linear regression line, where x is the independent variable and y is the dependent variable.
Formula	regr_avgy(y, x) - regr_slope(y, x) * regr_avgx(y, x)

regr_r2(y, x)

Description	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.
Formula	-

regr_slope(y, x)

Description	Returns the slope of the linear regression line, where x is the independent variable and y is the dependent variable.
Formula	regr_sxy(y, x) / regr_sxx(y, x)
Alias(es)	-

regr_sxx(y, x)

Description	The sample 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.
Formula	-

regr_sxy(y, x)

Description	The sample covariance, which includes Bessel's bias correction.
Formula	(sum(x*y) - sum(x) * sum(y) / regr_count(y, x)) / (regr_count(y, x) - 1), covar_pop(y, x) / (1 - 1 / regr_count(y, x))
Alias(es)	covar_samp(y, x)

regr_syy(y, x)

Description	The sample 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.
Formula	-

sem(x)

Description	The standard error of the mean.
Formula	-

skewness(x)

Description	The skewness.
Formula	-

stddev_pop(x)

Description	The population standard deviation.
Formula	sqrt(var_pop(x))

stddev_samp(x)

Description	The sample standard deviation.
Formula	sqrt(var_samp(x))
Alias(es)	stddev(x)

var_pop(x)

Description	The population variance, which does not include bias correction.
Formula	(sum(x^2) - sum(x)^2 / count(x)) / count(x), var_samp(y, x) * (1 - 1 / count(x))

var_samp(x)

Description	The sample variance, which includes Bessel's bias correction.
Formula	(sum(x^2) - sum(x)^2 / count(x)) / (count(x) - 1), var_pop(y, x) / (1 - 1 / count(x))
Alias(es)	variance(arg, val)

Counter and Histogram Aggregates

These aggregates read counters that only grow, gauges that go up and down, and histograms of bucket counts, such as the sums, gauges and histograms OpenTelemetry SDKs export. Each takes the time of the sample (TIMESTAMP, TIMESTAMP_NS or TIMESTAMPTZ) as its second argument and orders the samples by it, so the order of the input rows does not matter. Of several samples with the same time, the largest counts. Samples with a NULL value or time, an infinite time, or a NaN value are ignored. A counter that goes down was restarted: the first sample after the drop counts in full as the increase since the restart.

They return NULL for a group with fewer than two samples (otel_changes and otel_resets return 0 for a single sample), and they don't extrapolate: the change is measured from the first to the last sample of the group, so the change between the last sample of one time bucket and the first sample of the next is counted in neither. Over a window of the current and the previous row, otel_increase gives the change since the previous sample:

SQL

SELECT ts, otel_increase(total, ts) OVER (ORDER BY ts ROWS 1 PRECEDING) AS deltaFROM requestsORDER BY ts;

 ts                  | delta
---------------------+-------
 2026-01-01 00:00:00 |  NULL
 2026-01-01 00:00:10 |    60
 2026-01-01 00:00:20 |    20
 2026-01-01 00:00:30 |    30

otel_increase(value, ts)

The increase of the counter value from the first to the last sample, across restarts.

SQL

SELECT otel_increase(total, ts) AS increase,       round(otel_rate(total, ts), 6) AS rate,       otel_irate(total, ts) AS irateFROM requests;

 increase | rate     | irate
----------+----------+-------
      110 | 3.666667 |     3

otel_increase(bucket_counts, ts)

The increase of each bucket of a histogram whose bucket_counts, a list of per-bucket counts, only grow, as a DOUBLE[]. When any bucket goes down, the histogram was restarted and the sample after the drop counts in full. When the number of buckets changes, the increase up to that sample is dropped and the sample counts in full. A NULL element of bucket_counts counts as 0, and a sample with a NaN count is ignored.

SQL

SELECT otel_increase(bucket_counts, ts) AS bucketsFROM latencies;
SELECT otel_histogram_quantile(0.9, buckets, explicit_bounds) AS p90FROM (    SELECT otel_increase(bucket_counts, ts) AS buckets, any_value(explicit_bounds) AS explicit_bounds    FROM latencies);

 buckets
------------
 {10,4,1,0}

 p90
-------
 88.75

otel_rate(value, ts)

The per-second rate of the counter value: otel_increase(value, ts) divided by the seconds from the first to the last sample.

otel_irate(value, ts)

The per-second rate of the counter value between the last two samples.

otel_delta(value, ts)

The change of the gauge value from the first to the last sample.

otel_idelta(value, ts)

The change of the gauge value between the last two samples.

otel_deriv(value, ts)

The per-second slope of the gauge value, fitted by least squares over all samples. A value that never changes gives 0.

otel_predict_linear(value, ts, seconds)

The value the least-squares line through the samples of value reaches seconds after the last sample. The rows of a group must have the same seconds; otherwise the function fails.

otel_changes(value, ts)

The number of samples whose value differs from the previous sample, as a BIGINT.

otel_resets(value, ts)

The number of samples whose value is lower than the previous sample, the restarts of a counter, as a BIGINT.

SQL

SELECT otel_delta(free_gb, ts) AS delta,       otel_idelta(free_gb, ts) AS idelta,       round(otel_deriv(free_gb, ts), 6) AS per_second,       round(otel_predict_linear(free_gb, ts, 3600), 6) AS in_an_hour,       otel_changes(free_gb, ts) AS changes,       otel_resets(free_gb, ts) AS dropsFROM disk;

 delta | idelta | per_second | in_an_hour | changes | drops
-------+--------+------------+------------+---------+-------
    -6 |     -1 |    -0.0025 |         85 |       3 |     3

otel_histogram_quantile(q, bucket_counts, explicit_bounds)

The q-quantile of a histogram with explicit bucket bounds, the layout OpenTelemetry uses: bucket i counts the values up to explicit_bounds[i] and the last bucket the values above the last bound, so bucket_counts has one element more than explicit_bounds, and explicit_bounds must be strictly ascending, without NULL or NaN. The counts of all rows of a group are added up, so the rows must have the same q and explicit_bounds; otherwise the function fails.

The result is interpolated linearly inside the bucket that holds the quantile. The first bucket starts at 0, unless its bound is 0 or less, in which case the result is that bound. A quantile in the last bucket is the last bound. A q below 0 gives -Infinity and above 1 Infinity. A NULL element of bucket_counts counts as 0. Rows with empty bucket_counts or a NaN count are skipped, and a group with no observations or no bounds gives NULL.

SQL

SELECT otel_histogram_quantile(0.5, [10, 20, 10, 0], [1.0, 2.0, 4.0]) AS p50,       otel_histogram_quantile(0.95, [10, 20, 10, 0], [1.0, 2.0, 4.0]) AS p95;

 p50 | p95
-----+-----
 1.5 | 3.6

otel_histogram_fraction(lower, upper, bucket_counts, explicit_bounds)

The fraction of the observations of a histogram with explicit bucket bounds that lie between lower and upper, for example the share of requests that took at most 300 ms. Rows are added up as for otel_histogram_quantile, so they must have the same lower, upper and explicit_bounds.

Inside a bucket the observations are taken to be spread evenly, as for otel_histogram_quantile, and the first bucket starts at 0 when its bound is above 0. The last bucket, which has no upper bound, counts in full when upper is Infinity and not at all otherwise; a first bucket whose bound is 0 or less has no lower bound and counts in full when lower is -Infinity and not at all otherwise. A lower of -Infinity gives the fraction of observations up to upper. A lower at or above upper gives 0, a NaN bound gives NaN, and a group with no observations gives NULL.

SQL

SELECT otel_histogram_fraction('-inf'::DOUBLE, 2, [10, 20, 10, 0], [1.0, 2.0, 4.0]) AS at_most_2,       round(otel_histogram_stddev([10, 20, 10, 0], [1.0, 2.0, 4.0]), 6) AS stddev;

 at_most_2 | stddev
-----------+----------
      0.75 | 0.892679

otel_histogram_stddev(bucket_counts, explicit_bounds)

The estimated population standard deviation of the observations of a histogram with explicit bucket bounds. Each observation is taken to lie at the middle of its bucket: the first bucket at half its bound (at its bound when that is 0 or less), the last bucket at the last bound. Rows are added up as for otel_histogram_quantile. A group with no observations gives NULL.

otel_histogram_stdvar(bucket_counts, explicit_bounds)

The square of otel_histogram_stddev: the estimated population variance.

otel_exponential_histogram(scale, zero_count, positive_offset, positive_bucket_counts, negative_offset, negative_bucket_counts)

Packs the columns of an exponential histogram, the layout OpenTelemetry uses for base-2 exponential histograms, into one value for the aggregates below. Bucket i of the positive side counts the values above 2^(i·2^-scale) up to 2^((i+1)·2^-scale); positive_bucket_counts[k] is bucket positive_offset + k. The negative side holds the same buckets for the absolute values of negative observations, and zero_count counts the zeros. A NULL zero_count, offset, list or list element counts as 0 or empty; a value with a NULL scale or a NaN count is ignored by the aggregates, and a scale outside -10 to 20, or a bucket beyond the range of DOUBLE values at that scale, makes them fail.

otel_histogram_quantile(q, histogram)

The q-quantile of the exponential histograms of a group, added up. Rows at different scales are added at the coarsest of them, and at a coarser scale still when their buckets would otherwise span more than 4096 buckets on one side. Inside a bucket the result is interpolated on a logarithmic scale; a quantile among the zeros is 0. The rows of a group must have the same q. The other edge cases are those of otel_histogram_quantile for explicit bounds.

SQL

SELECT round(otel_histogram_quantile(0.5, h), 6) AS p50,       otel_histogram_fraction(0, 4, h) AS above_0_up_to_4FROM (SELECT otel_exponential_histogram(0, 1, 0, [1, 2, 4], 1, [2]) AS h);

 p50      | above_0_up_to_4
----------+-----------------
 2.828427 |             0.3

otel_histogram_fraction(lower, upper, histogram)

The fraction of the observations between lower and upper, interpolated on a logarithmic scale inside a bucket. Zeros count when lower is below 0 and upper at or above 0. The edge cases are those of otel_histogram_fraction for explicit bounds.

otel_histogram_stddev(histogram) and otel_histogram_stdvar(histogram)

The estimated population standard deviation and variance of the observations, each observation taken to lie at the geometric mean of its bucket's bounds and the zeros at 0.

otel_increase(histogram, ts)

The increase of each bucket of an exponential histogram whose counts only grow, as an exponential histogram value: the samples are compared at the coarsest scale among them, and when any bucket or zero_count goes down, the histogram was restarted and the sample after the drop counts in full. Buckets with no increase at either end are left out.

SQL

SELECT h.zero_count, h.positive_offset, h.positive_bucket_counts,       round(otel_histogram_quantile(0.5, h), 6) AS p50FROM (    SELECT otel_increase(otel_exponential_histogram(scale, zero_count, positive_offset, positive_bucket_counts,                                                    negative_offset, negative_bucket_counts), ts) AS h    FROM rpc)GROUP BY ALL;

 zero_count | positive_offset | positive_bucket_counts | p50
------------+-----------------+------------------------+----------
          1 |               0 | {2,0,2}                | 1.681793

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.

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)])

Aggregate Aliases

These names are alternative spellings of the aggregates above, kept so that queries written for PostgreSQL, DuckDB or other dialects run unchanged. Each behaves exactly like the function it aliases.

Function	Description
argmax(arg, val)	Alias of arg_max(arg, val).
argmin(arg, val)	Alias of arg_min(arg, val).
array_agg(arg)	Alias of list(arg).
count_star()	Alias of count().
group_concat(arg, sep)	Alias of string_agg(arg, sep).
listagg(arg, sep)	Alias of string_agg(arg, sep).
max_by(arg, val)	Alias of arg_max(arg, val).
mean(arg)	Alias of avg(arg).
min_by(arg, val)	Alias of arg_min(arg, val).
stddev(x)	Alias of stddev_samp(x).
sumkahan(arg)	Alias of kahan_sum(arg).
variance(x)	Alias of var_samp(x).

Miscellaneous Aggregate Functions

Function	Description	Alias
grouping()	For queries with GROUP BY and either ROLLUP or GROUPING SETS: Returns an integer identifying which of the argument expressions were used to group on to create the current super-aggregate row.	grouping_id()