SQL aggregate functions

SQL aggregate functions aggregate values in a specified column for each group or SQL partition and return a single row per group containing the aggregate value.

• General aggregate functions
  • array_agg
  • avg
  • bit_and
  • bit_or
  • bit_xor
  • bool_and
  • bool_or
  • count
  • first_value
  • grouping
  • last_value
  • max
  • mean
  • median
  • min
  • nth_value
  • string_agg
  • sum
• Statistical aggregate functions
  • corr
  • covar
  • covar_pop
  • covar_samp
  • regr_avgx
  • regr_avgy
  • regr_count
  • regr_intercept
  • regr_r2
  • regr_slope
  • regr_sxx
  • regr_syy
  • regr_sxy
  • stddev
  • stddev_pop
  • stddev_samp
  • var
  • var_pop
  • var_population
  • var_samp
  • var_sample
• Approximate aggregate functions
  • approx_distinct
  • approx_median
  • approx_percentile_cont
  • approx_percentile_cont_with_weight

General aggregate functions

• array_agg
• avg
• bit_and
• bit_or
• bit_xor
• bool_and
• bool_or
• count
• first_value
• grouping
• last_value
• max
• mean
• median
• min
• nth_value
• string_agg
• sum

array_agg

Returns an array created from the expression elements.

array_agg(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT
  room,
  array_agg(temp)[3] AS '3rd_temp'
FROM home
GROUP BY room

avg

Returns the average of numeric values in the specified column.

avg(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

Aliases

• mean

SELECT 
  location,
  avg(precip) AS avg_precip
FROM weather
GROUP BY location

bit_and

Computes the bitwise AND of all non-null input values.

bit_and(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  bit_and(precip::BIGINT) AS precip_bit_and
FROM weather
GROUP BY location

bit_or

Computes the bitwise OR of all non-null input values.

bit_or(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  bit_or(precip::BIGINT) AS precip_bit_or
FROM weather
GROUP BY location

bit_xor

Computes the bitwise exclusive OR of all non-null input values.

bit_xor(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  bit_xor(precip::BIGINT) AS precip_bit_xor
FROM weather
GROUP BY location

bool_and

Returns true if all non-null input values are true, otherwise returns false.

bool_and(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  bool_and(precip > 0) AS precip_bool_and
FROM weather
GROUP BY location

bool_or

Returns true if any non-null input value is true, otherwise returns false.

bool_or(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  bool_or(precip > 0) AS precip_bool_or
FROM weather
GROUP BY location

count

Returns the number of rows in the specified column.

Count includes null values in the total count. To exclude null values from the total count, include <column> IS NOT NULL in the WHERE clause.

count(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  count(precip) AS precip_count
FROM weather
GROUP BY location

first_value

Returns the first element in an aggregation group according to the specified ordering. If no ordering is specified, returns an arbitrary element from the group.

first_value(expression [ORDER BY expression])

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  first_value(temp_max ORDER BY time) AS temp_max_first_value
FROM weather
GROUP BY location

grouping

Returns 1 if the data is aggregated across the specified column, or 0 if it is not aggregated in the result set.

grouping(expression)

Arguments

• expression: Expression to evaluate whether data is aggregated across the specified column. Can be a constant, column, or function.

SELECT 
  location,
  avg(temp_max) AS avg_max_temp,
  grouping(location) AS grouping
FROM weather
GROUP BY GROUPING SETS ((location), ())

last_value

Returns the last element in an aggregation group according to the specified ordering. If no ordering is specified, returns an arbitrary element from the group.

last_value(expression [ORDER BY expression])

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  last_value(temp_max ORDER BY time) AS temp_max_last_value
FROM weather
GROUP BY location

max

Returns the maximum value in the specified column.

max(expression)

To return both the maximum value and its associated timestamp, use selector_max.

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  max(precip) AS max_precip
FROM weather
GROUP BY location

mean

Alias of avg.

median

Returns the median value in the specified column.

median(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  median(temp_avg) AS median_temp_avg
FROM weather
GROUP BY location

min

Returns the minimum value in the specified column.

min(expression)

To return both the minimum value and its associated timestamp, use selector_max.

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  min(temp_min) AS min_temp_min
FROM weather
GROUP BY location

nth_value

Returns the nth value in a group of values.

nth_value(expression, n [ORDER BY order_expression_1, ... order_expression_n])

arguments

• expression: The column or expression to retrieve the nth value from.
• n: The position (nth) of the value to retrieve, based on the ordering.
• order_expression_1, … order_expression_n: Expressions to order by. Can be a column or function, and any combination of arithmetic operators.

SELECT
  room,
  nth_value(temp, 3 ORDER BY time) AS "3rd_temp"
FROM
  home
GROUP BY
  room

string_agg

Concatenates the values of string expressions and places separator values between them.

string_agg(expression, delimiter)

Arguments

• expression: The string expression to concatenate. Can be a column or any valid string expression.
• delimiter: A literal string to use as a separator between the concatenated values.

SELECT
  location,
  string_agg(temp_avg::STRING, ', ') AS string_agg
FROM 
  weather
WHERE
  time > '2020-01-01T00:00:00Z'
  AND time < '2020-01-05T00:00:00Z'
GROUP BY
  location

sum

Returns the sum of all values in the specified column.

sum(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  sum(precip) AS total_precip
FROM weather
GROUP BY location

Statistical aggregate functions

• corr
• covar
• covar_pop
• covar_samp
• regr_avgx
• regr_avgy
• regr_count
• regr_intercept
• regr_r2
• regr_slope
• regr_sxx
• regr_syy
• regr_sxy
• stddev
• stddev_pop
• stddev_samp
• var
• var_pop
• var_population
• var_samp
• var_sample

corr

Returns the coefficient of correlation between two numeric values.

corr(expression1, expression2)

Arguments

• expression1: First column or literal value to operate on.
• expression2: Second column or literal value to operate on.

SELECT
  room,
  corr(hum, temp) AS correlation
FROM home
GROUP BY room

covar

Returns the covariance of a set of number pairs.

covar(expression1, expression2)

Arguments

• expression1: First column or literal value to operate on.
• expression2: Second column or literal value to operate on.

SELECT
  room,
  covar(hum, temp) AS covar
FROM home
GROUP BY room

covar_pop

Returns the population covariance of a set of number pairs.

covar_pop(expression1, expression2)

Arguments

• expression1: First column or literal value to operate on.
• expression2: Second column or literal value to operate on.

SELECT
  room,
  covar_pop(hum, temp) AS covar_pop
FROM home
GROUP BY room

covar_samp

Returns the sample covariance of a set of number pairs.

covar_samp(expression1, expression2)

Arguments

• expression1: First column or literal value to operate on.
• expression2: Second column or literal value to operate on.

SELECT
  room,
  covar_samp(hum, temp) AS covar_samp
FROM home
GROUP BY room

regr_avgx

Computes the average of the independent variable (input), expression_x, for the non-null dependent variable, expression_y.

regr_avgx(expression_y, expression_x)

Arguments

• expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  regr_avgx(temp_min, temp_max) AS temp_regr_avgx
FROM weather
GROUP BY location

regr_avgy

Computes the average of the dependent variable (output), expression_y, for the non-null dependent variable, expression_y.

regr_avgy(expression_y, expression_x)

Arguments

• expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  regr_avgy(temp_min, temp_max) AS temp_regr_avgy
FROM weather
GROUP BY location

regr_count

Counts the number of non-null paired data points.

regr_count(expression_y, expression_x)

Arguments

• expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  regr_count(temp_min, temp_max) AS temp_regr_count
FROM weather
GROUP BY location

regr_intercept

Computes the y-intercept of the linear regression line. For the equation (y = kx + b), this function returns b.

regr_intercept(expression_y, expression_x)

Arguments

• expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  regr_intercept(temp_min, temp_max) AS temp_regr_intercept
FROM weather
GROUP BY location

regr_r2

Computes the square of the correlation coefficient between the independent and dependent variables.

regr_r2(expression_y, expression_x)

Arguments

• expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  regr_r2(temp_min, temp_max) AS temp_regr_r2
FROM weather
GROUP BY location

regr_slope

Returns the slope of the linear regression line for non-null pairs in aggregate columns. Given input column Y and X: regr_slope(Y, X) returns the slope (k in Y = k*X + b) using minimal RSS fitting.

regr_slope(expression_y, expression_x)

Arguments

• expression_y: Y expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_x: X expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  regr_slope(temp_min, temp_max) AS temp_regr_slope
FROM weather
GROUP BY location

regr_sxx

Computes the sum of squares of the independent variable.

regr_sxx(expression_y, expression_x)

Arguments

• expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  regr_sxx(temp_min, temp_max) AS temp_regr_sxx
FROM weather
GROUP BY location

regr_syy

Computes the sum of squares of the dependent variable.

regr_syy(expression_y, expression_x)

Arguments

• expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  regr_syy(temp_min, temp_max) AS temp_regr_syy
FROM weather
GROUP BY location

regr_sxy

Computes the sum of products of paired data points.

regr_sxy(expression_y, expression_x)

Arguments

• expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT 
  location,
  regr_sxy(temp_min, temp_max) AS temp_regr_sxy
FROM weather
GROUP BY location

stddev

Returns the standard deviation of a set of numbers.

stddev(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT
  room,
  stddev(co) AS stddev
FROM home
GROUP BY room

stddev_pop

Returns the population standard deviation of a set of numbers.

stddev_pop(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT
  room,
  stddev_pop(co) AS stddev_pop
FROM home
GROUP BY room

stddev_samp

Returns the sample standard deviation of a set of numbers.

stddev_samp(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT
  room,
  stddev_samp(co) AS stddev_samp
FROM home
GROUP BY room

var

Returns the statistical variance of a set of numbers.

var(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT
  room,
  var(co) AS var
FROM home
GROUP BY room

var_pop

Returns the statistical population variance of a set of numbers.

var_pop(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

Aliases

• var_population

SELECT
  room,
  var_pop(co) AS var_pop
FROM home
GROUP BY room

var_population

Alias of var_pop.

var_samp

Returns the statistical sample variance of a set of numbers.

var_samp(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

Aliases

• var_sample

SELECT
  room,
  var_samp(co) AS var_samp
FROM home
GROUP BY room

var_sample

Alias of var_samp.

Approximate aggregate functions

• approx_distinct
• approx_median
• approx_percentile_cont
• approx_percentile_cont_with_weight

approx_distinct

Returns the approximate number of distinct input values calculated using the HyperLogLog algorithm.

approx_distinct(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT
  room,
  approx_distinct(co::string) AS approx_distinct
FROM home
GROUP BY room

approx_median

Returns the approximate median (50th percentile) of input values. It is an alias of approx_percentile_cont(x, 0.5).

approx_median(expression)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT
  room,
  approx_median(temp) AS approx_median
FROM home
GROUP BY room

approx_percentile_cont

Returns the approximate percentile of input values using the t-digest algorithm.

approx_percentile_cont(expression, percentile, centroids)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

• percentile: Percentile to compute. Must be a float value between 0 and 1 (inclusive).

• centroids: Number of centroids to use in the t-digest algorithm. Default is 100.

  If there are this number or fewer unique values, you can expect an exact result. A higher number of centroids results in a more accurate approximation, but requires more memory to compute.

SELECT
  room,
  approx_percentile_cont(temp, 0.99) AS "99th_percentile"
FROM home
GROUP BY room

approx_percentile_cont_with_weight

Returns the weighted approximate percentile of input values using the t-digest algorithm.

approx_percentile_cont_with_weight(expression, weight, percentile)

Arguments

• expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
• weight: Expression to use as weight. Can be a constant, column, or function, and any combination of arithmetic operators.
• percentile: Percentile to compute. Must be a float value between 0 and 1 (inclusive).

SELECT
  room,
  approx_percentile_cont_with_weight(temp, co, 0.99) AS "co_weighted_99th_percentile"
FROM home
GROUP BY room