Compatible with PostgreSQL, Hologres allows you to use standard PostgreSQL syntax for data development.

The following table describes the general-purpose aggregate functions supported by Hologres. The functions supported by Hologres are only a subset of the PostgreSQL functions. For more information about how to use these functions, see Aggregate Functions in the PostgreSQL documentation.
Function Description Example Result
array_agg(bigint) Aggregates the values of a BIGINT expression to an array. array_agg(c1) {1,2}
array_agg(bool) Aggregates the values of a BOOLEAN expression to an array. array_agg(c1) {true,false}
array_agg(text) Aggregates the values of a TEXT expression to an array. array_agg(c1) {a,b}
array_agg(float8) Aggregates the values of a FLOAT8 expression to an array. array_agg(c1) {1.1,2.2}
array_agg(float4) Aggregates the values of a FLOAT4 expression to an array. array_agg(c1) {1.1,2.2}
array_agg(int) Aggregates the values of an INT expression to an array. array_agg(c1) {1, 2}
avg(bigint) Calculates the average of the non-NULL values of a BIGINT expression. avg(c1) 2.000000
avg(float8) Calculates the average of the non-NULL values of a FLOAT8 expression. avg(c1) 2.000000
avg(float4) Calculates the average of the non-NULL values of a FLOAT4 expression. avg(c1) 2.000000
avg(int) Calculates the average of the non-NULL values of an INT expression. avg(c1) 2.000000
bit_and(bigint) Performs bitwise AND operations on the non-NULL values of a BIGINT expression. bit_and(c1) 0
bit_and(int) Performs bitwise AND operations on the non-NULL values of an INT expression. bit_and(c1) 0
bit_or(bigint) Performs bitwise OR operations on the non-NULL values of a BIGINT expression. bit_or(c1) 3
bit_or(int) Performs bitwise OR operations on the non-NULL values of an INT expression. bit_or(c1) 3
bool_and(bool) Performs AND operations on a BOOLEAN expression. This function returns true if all the values returned by the expression are true and returns false if one or more values are false. bool_and(c1) f
bool_or(bool) Performs OR operations on a BOOLEAN expression. This function returns true if one or more values returned by the expression are true and returns false if no value is false. bool_or(c1) t
count(*) Returns the number of rows in a table. count(*) 3
count(bigint) Returns the number of rows defined by a BIGINT expression.
Note This function ignores NULL values.
count(c1) 3
count(numeric) Returns the number of rows defined by a NUMERIC expression.
Note This function ignores NULL values.
count(c1) 3
every(bool) Performs AND operations on a BOOLEAN expression. This function returns true if all the values returned by the expression are true and returns false if one or more values are false. N/A N/A
max(bigint) Returns the maximum value returned by a BIGINT expression. max(c1) 3
max(float8) Returns the maximum value returned by a FLOAT8 expression. max(c1) 3.0
max(float4) Returns the maximum value returned by a FLOAT4 expression. max(c1) 3.0
max(int) Returns the maximum value returned by an INT expression. max(c1) 3
max(numeric) Returns the maximum value returned by a NUMERIC expression. max(c1) 3.0
min(bigint) Returns the minimum value returned by a BIGINT expression. min(c1) 1
min(float8) Returns the minimum value returned by a FLOAT8 expression. min(c1) 1.0
min(float4) Returns the minimum value returned by a FLOAT4 expression. min(c1) 1.0
min(int) Returns the minimum value returned by an INT expression. min(c1) 1
min(numeric) Returns the minimum value returned by a NUMERIC expression. min(c1) 1.0
sum(bigint) Returns the sum of the values returned by a BIGINT expression. sum(c1) 6
sum(float8) Returns the sum of the values returned by a FLOAT8 expression. sum(c1) 6.0
sum(float4) Returns the sum of the values returned by a FLOAT4 expression. sum(c1) 6.0
sum(int) Returns the sum of the values returned by an INT expression. sum(c1) 6
sum(numeric) Returns the sum of the values returned by a NUMERIC expression. sum(c1) 6.0
string_agg(expression, delimiter) Concatenates the non-NULL values of an expression into a string by using a delimiter. string_agg(c1, '-') a-b-c
corr(Y, X) Returns the correlation coefficient of input values. corr(c1, c2) N/A
covar_pop(Y, X) Returns the population covariance of input values. covar_pop(c1, c2) N/A
covar_samp(Y, X) Returns the sample covariance of input values. covar_samp(c1, c2) N/A
regr_avgx(Y, X) Calculates the average of the independent variable. regr_avgx(c1, c2) N/A
regr_avgy(Y, X) Calculates the average of the dependent variable. regr_avgy(c1, c2) N/A
regr_count(Y, X) Returns the number of non-NULL data pairs of the dependent and independent variables. regr_count(c1, c2) N/A
regr_intercept(Y, X) Returns the intercept on the y-axis of the linear regression line that best fits the dependent and independent variables. regr_intercept(c1, c2) N/A
regr_r2(Y, X) Returns the square of the correlation coefficient. regr_r2(c1, c2) N/A
regr_slope(Y, X) Returns the slope of the linear regression line that best fits the dependent and independent variables. regr_slope(c1, c2) N/A
regr_sxx(Y, X) Returns the sum of the squares of the independent variable by using this formula: sum(X^2) - sum(X)^2/N. regr_sxx(c1, c2) N/A
regr_sxy(Y, X) Returns the sum of the products of the dependent and independent variables by using this formula: sum(X × Y) - sum(X) × sum(Y)/N. regr_sxy(c1, c2) N/A
regr_syy(Y, X) Returns the sum of the squares of the dependent variable by using this formula: sum(Y^2) - sum(Y)^2/N. regr_syy(c1, c2) N/A
stddev(int) Calculates the sample standard deviation of the values returned by an INT expression. stddev(c1) N/A
stddev(numeric) Calculates the sample standard deviation of the values returned by a NUMERIC expression. stddev(c1) N/A
stddev(float8) Calculates the sample standard deviation of the values returned by a FLOAT8 expression. stddev(c1) N/A
stddev_pop(int) Calculates the population standard deviation of the values returned by an INT expression. stddev_pop(c1) N/A
stddev_pop(numeric) Calculates the population standard deviation of the values returned by a NUMERIC expression. stddev_pop(c1) N/A
stddev_pop(float8) Calculates the population standard deviation of the values returned by a FLOAT8 expression. stddev_pop(c1) N/A
stddev_samp(int) Calculates the sample standard deviation of the values returned by an INT expression. stddev_samp(c1) N/A
stddev_samp(numeric) Calculates the sample standard deviation of the values returned by a NUMERIC expression. stddev_samp(c1) N/A
stddev_samp(float8) Calculates the sample standard deviation of the values returned by a FLOAT8 expression. stddev_samp(c1) N/A
variance(int) Calculates the sample variance of the values returned by an INT expression. variance(c1) N/A
variance(numeric) Calculates the sample variance of the values returned by a NUMERIC expression. variance(c1) N/A
var_pop(float8) Calculates the population variance of the values returned by a FLOAT8 expression. var_pop(c1) N/A
var_pop(int) Calculates the population variance of the values returned by an INT expression. var_pop(c1) N/A
var_pop(numeric) Calculates the population variance of the values returned by a NUMERIC expression. var_pop(c1) N/A
var_samp(float8) Calculates the sample variance of the values returned by a FLOAT8 expression. var_samp(c1) N/A
var_samp(int) Calculates the sample variance of the values returned by an INT expression. var_samp(c1) N/A
var_samp(numeric) Calculates the sample variance of the values returned by a NUMERIC expression. var_samp(c1) N/A