11th October 2018: PostgreSQL 11 RC 1 Released!
Supported Versions: Current (10) / 9.6 / 9.5 / 9.4 / 9.3
Development Versions: 11 / devel
Unsupported versions: 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1

# 9.18. Aggregate Functions

Aggregate functions compute a single result value from a set of input values. The built-in aggregate functions are listed in Table 9-41 and Table 9-42. The special syntax considerations for aggregate functions are explained in Section 4.2.7. Consult Section 2.7 for additional introductory information.

Table 9-41. General-Purpose Aggregate Functions

Function Argument Type Return Type Description
`avg(expression)` smallint, int, bigint, real, double precision, numeric, or interval numeric for any integer type argument, double precision for a floating-point argument, otherwise the same as the argument data type the average (arithmetic mean) of all input values
`bit_and(expression)` smallint, int, bigint, or bit same as argument data type the bitwise AND of all non-null input values, or null if none
`bit_or(expression)` smallint, int, bigint, or bit same as argument data type the bitwise OR of all non-null input values, or null if none
`bool_and(expression)` bool bool true if all input values are true, otherwise false
`bool_or(expression)` bool bool true if at least one input value is true, otherwise false
`count(*)`   bigint number of input rows
`count(expression)` any bigint number of input rows for which the value of expression is not null
`every(expression)` bool bool equivalent to `bool_and`
`max(expression)` any array, numeric, string, or date/time type same as argument type maximum value of expression across all input values
`min(expression)` any array, numeric, string, or date/time type same as argument type minimum value of expression across all input values
`sum(expression)` smallint, int, bigint, real, double precision, numeric, or interval bigint for smallint or int arguments, numeric for bigint arguments, double precision for floating-point arguments, otherwise the same as the argument data type sum of expression across all input values

It should be noted that except for `count`, these functions return a null value when no rows are selected. In particular, `sum` of no rows returns null, not zero as one might expect. The `coalesce` function can be used to substitute zero for null when necessary.

Note: Boolean aggregates `bool_and` and `bool_or` correspond to standard SQL aggregates `every` and `any` or `some`. As for `any` and `some`, it seems that there is an ambiguity built into the standard syntax:

```SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
```

Here `ANY` can be considered both as leading to a subquery or as an aggregate if the select expression returns 1 row. Thus the standard name cannot be given to these aggregates.

Note: Users accustomed to working with other SQL database management systems might be surprised by the performance of the `count` aggregate when it is applied to the entire table. A query like:

```SELECT count(*) FROM sometable;
```

will be executed by PostgreSQL using a sequential scan of the entire table.

Table 9-42 shows aggregate functions typically used in statistical analysis. (These are separated out merely to avoid cluttering the listing of more-commonly-used aggregates.) Where the description mentions N, it means the number of input rows for which all the input expressions are non-null. In all cases, null is returned if the computation is meaningless, for example when N is zero.

Table 9-42. Aggregate Functions for Statistics

Function Argument Type Return Type Description
```corr(Y, X)``` double precision double precision correlation coefficient
```covar_pop(Y, X)``` double precision double precision population covariance
```covar_samp(Y, X)``` double precision double precision sample covariance
`regr_avgx(Y, X)` double precision double precision average of the independent variable (sum(X)/N)
`regr_avgy(Y, X)` double precision double precision average of the dependent variable (sum(Y)/N)
`regr_count(Y, X)` double precision bigint number of input rows in which both expressions are nonnull
`regr_intercept(Y, X)` double precision double precision y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs
`regr_r2(Y, X)` double precision double precision square of the correlation coefficient
```regr_slope(Y, X)``` double precision double precision slope of the least-squares-fit linear equation determined by the (X, Y) pairs
`regr_sxx(Y, X)` double precision double precision sum(X^2) - sum(X)^2/N ("sum of squares" of the independent variable)
`regr_sxy(Y, X)` double precision double precision sum(X*Y) - sum(X) * sum(Y)/N ("sum of products" of independent times dependent variable)
`regr_syy(Y, X)` double precision double precision sum(Y^2) - sum(Y)^2/N ("sum of squares" of the dependent variable)
`stddev(expression)` smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric historical alias for `stddev_samp`
`stddev_pop(expression)` smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric population standard deviation of the input values
`stddev_samp(expression)` smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric sample standard deviation of the input values
`variance`(expression) smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric historical alias for `var_samp`
`var_pop`(expression) smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric population variance of the input values (square of the population standard deviation)
`var_samp`(expression) smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric sample variance of the input values (square of the sample standard deviation)