PostgreSQL Aggregate Functions

Aggregate functions compute a single result from a set of input values, below table shows the aggregate functions and respective examples. Aggregate functions are very useful in many scenarios, for example, string_agg concatenates all string in one go without writing multiple subquery or in-line query

General Purpose Aggregate Operators

array_agg ( anynonarray ) → anyarray
Collects all the input values, including nulls, into an array
select array_agg(array[1,2]); → {{1,2}}
avg ( smallint ) → numeric
avg ( integer ) → numeric
avg ( bigint ) → numeric
avg ( numeric ) → numeric
avg ( real ) → double precision
avg ( double precision ) → double precision
avg ( interval ) → interval
Computes the average (arithmetic mean) of all the non-null input values
select avg(salary) from emp; → 9088.88
bit_and ( smallint ) → smallint
bit_and ( integer ) → integer
bit_and ( bigint ) → bigint
bit_and ( bit ) → bit
Computes the bitwise AND of all non-null input values
select bit_and(2); → 2
select bit_and(14::integer); → 14
select bit_and(14::bit); → 0
bit_or ( smallint ) → smallint
bit_or ( integer ) → integer
bit_or ( bigint ) → bigint
bit_or ( bit ) → bit
Computes the bitwise OR of all non-null input values
select bit_or(2); → 2
select bit_or(14::integer); → 14
select bit_or(14::bit); → 0
bit_xor ( smallint ) → smallint
bit_xor ( integer ) → integer
bit_xor ( bigint ) → bigint
bit_xor ( bit ) → bit
Computes the bitwise exclusive OR of all non-null input values.
select bit_xor(2); → 2
select bit_xor(14::integer); → 14
select bit_xor(14::bit); → 0
bool_and ( boolean ) → boolean
Returns true if all non-null input values are true, otherwise false
select bool_and(True); → True
select bool_and(False); → False
select bool_and(‘t’); → True
select bool_and(‘f’); → False
bool_or ( boolean ) → boolean
Returns true if any non-null input value is true, otherwise false
select bool_or(True); → True
select bool_or(False); → False
select bool_or(‘t’); → True
select bool_or(‘f’); → False
count ( * ) → bigint
Computes the number of input rows
select count(*) from emp; → 9
count ( “any” ) → bigint
Computes the number of input rows in which the input value is not null
select count(empno) from emp; → 9
every ( boolean ) → boolean
This is the SQL standard’s equivalent to bool_and
select every(True); → True
json_agg ( anyelement ) → json
jsonb_agg ( anyelement ) → jsonb
Collects all the input values, including nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb
select json_agg(5); → [5]
select jsonb_agg(5); → [5]
json_object_agg ( key “any”, value “any” ) → json
jsonb_object_agg ( key “any”, value “any” ) → jsonb
Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. Values can be null, but not keys
select json_object_agg(‘company’,’google’); → { “company” : “google” }
select jsonb_object_agg(‘company’,’google’);
→ { “company” : “google” }
max ( see text ) → same as input type
Computes the maximum of the non-null input values. Available for any numeric, string, date/time, or enum type, as well as inet, interval, money, oid, pg_lsn, tid, and arrays of any of these types
select max(salary) from emp ; → 12000
min ( see text ) → same as input type
Computes the minimum of the non-null input values. Available for any numeric,
string, date/time, or enum type, as well as inet, interval, money, oid,
pg_lsn, tid, and arrays of any of these types
select min(salary) from emp ; → 7500
range_agg ( value anyrange ) → anymultirange
Computes the union of the non-null input values
select range_agg(numrange(1,10,'[]’)); → {[1,10]}
range_intersect_agg ( value anyrange ) → anyrange
range_intersect_agg ( value anymultirange ) → anymultirange
Computes the intersection of the non-null input values
select range_intersect_agg(numrange(1,10,'[]’)); → [1,10]
string_agg ( value text, delimiter text ) → text
string_agg ( value bytea, delimiter bytea ) → bytea
Concatenates the non-null input values into a string. Each value after the first is
preceded by the corresponding delimiter (if it’s not null).
select string_agg(empname,’,’) from emp;
Bill,Solomon,Susan,Wendy,Benjamin,Tom,
Henry,Robert,Paul
sum ( smallint ) → bigint
sum ( integer ) → bigint
sum ( bigint ) → numeric
sum ( numeric ) → numeric
sum ( real ) → real
sum ( double precision ) → double precision
sum ( interval ) → interval
sum ( money ) → money
Computes the sum of the non-null input values.
select sum(salary) from emp ; → 81800
xmlagg ( xml ) → xml
Concatenates the non-null XML input values
select xmlagg(x) from test; → “<foo>abc</foo>”

Aggregate Functions for Statistics

postgres=# select empno,salary,bonus from emp;
 empno | salary | bonus 
-------+--------+-------
    11 |  10000 |   650
    10 |  12000 |   750
    12 |  10000 |   900
    13 |   9000 |   800
    14 |   7500 |  1000
    15 |   7600 |   850
    16 |   8500 |   950
    17 |   9500 |   600
    18 |   7700 |   700
(9 rows)
corr ( Y double precision, X double precision ) → double precision
Computes the correlation coefficient
select corr(salary,bonus) from emp; → -0.3814557165761751
covar_pop ( Y double precision, X double precision ) → double
precision
Computes the population covariance
select covar_pop(salary,bonus) from emp;
-68333.33333333333
covar_samp ( Y double precision, X double precision ) → double
precision
Computes the sample covariance
select covar_samp(salary,bonus) from emp; → -76875
regr_avgx ( Y double precision, X double precision ) → double
precision
Computes the average of the independent variable, sum(X)/N
select regr_avgx(salary,bonus) from emp; → 800
regr_avgy ( Y double precision, X double precision ) → double
precision
Computes the average of the dependent variable, sum(Y)/N
select regr_avgy(salary,bonus) from emp; → 9088.888888888889
regr_count ( Y double precision, X double precision ) → bigint
Computes the number of rows in which both inputs are non-null
select regr_count(salary,bonus) from emp; → 9
regr_intercept ( Y double precision, X double precision ) → double
precision
Computes the y-intercept of the least-squares-fit linear equation determined by
the (X, Y) pairs
select regr_intercept(salary,bonus) from emp; → 12368.888888888889
regr_r2 ( Y double precision, X double precision ) → double precision
Computes the square of the correlation coefficient
select regr_r2(salary,bonus) from emp; → 0.1455084637086432
regr_slope ( Y double precision, X double precision ) → double
precision
Computes the slope of the least-squares-fit linear equation determined by the (X,
Y) pairs
select regr_slope(salary,bonus) from emp; → -4.1
regr_sxx ( Y double precision, X double precision ) → double
precision
Computes the “sum of squares” of the independent variable, sum(X^2) –
sum(X)^2/N.
select regr_sxx(salary,bonus) from emp; → 150000
regr_sxy ( Y double precision, X double precision ) → double
precision
Computes the “sum of products” of independent times dependent variables,
sum(X*Y) – sum(X) * sum(Y)/N
select regr_sxy(salary,bonus) from emp; → -615000
regr_syy ( Y double precision, X double precision ) → double
precision
Computes the “sum of squares” of the dependent variable, sum(Y^2) –
sum(Y)^2/N
select regr_syy(salary,bonus) from emp; → 17328888.88888888
stddev ( numeric_type ) → double precision for real or double
precision, otherwise numeric
This is a historical alias for stddev_samp
select stddev(salary) from emp; → 1471.771419450422
stddev_pop ( numeric_type ) → double precision for real or double
precision, otherwise numeric
Computes the population standard deviation of the input values
select stddev_pop(salary) from emp; → 1387.599401399926
stddev_samp ( numeric_type ) → double precision for real or double
precision, otherwise numeric
Computes the sample standard deviation of the input values

select stddev_samp(salary) from emp; → 1471.771419450422
variance ( numeric_type ) → double precision for real or double
precision, otherwise numeric
select variance(salary) from emp; → 2166111.111111111111
var_pop ( numeric_type ) → double precision for real or double
precision, otherwise numeric
Computes the population variance of the input values (square of the population
standard deviation).
select var_pop(salary) from emp; → 1925432.098765432099
var_samp ( numeric_type ) → double precision for real or double
precision, otherwise numeric
Computes the sample variance of the input values (square of the sample standard
deviation).
select var_samp(salary) from emp; → 2166111.111111111111