DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK

# 9.15. Aggregate Functions

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

Table 9-37. Aggregate Functions

FunctionArgument TypeReturn TypeDescription
`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(*)` bigintnumber of input values
`count(expression)`anybigint number of input values 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 typesame as argument type maximum value of expression across all input values
`min(expression)`any array, numeric, string, or date/time typesame as argument type minimum value of expression across all input values
`stddev(expression)` smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric sample standard deviation of the 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
`variance`(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)

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