Documentação do PostgreSQL 8.2.0
Anterior	Início	Capítulo 9. Functions and Operators	Fim	Próxima

9.15. Aggregate Functions

Aggregate functions compute a single result value from a set of input values. The built-in aggregate functions are listed in Tabela 9-37 and Tabela 9-38. The special syntax considerations for aggregate functions are explained in Seção 4.2.7. Consult Seção 2.7 for additional introductory information.

Tabela 9-37. General-Purpose Aggregate Functions

Function	Argument Type	Return Type	Description
avg(expressão)	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(expressão)	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(expressão)	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(expressão)	bool	bool	true if all input values are true, otherwise false
bool_or(expressão)	bool	bool	true if at least one input value is true, otherwise false
count(*)		bigint	number of input rows
count(expressão)	any	bigint	number of input rows for which the value of expression is not null
every(expressão)	bool	bool	equivalent to bool_and
max(expressão)	any array, numeric, string, or date/time type	same as argument type	maximum value of expression across all input values
min(expressão)	any array, numeric, string, or date/time type	same as argument type	minimum value of expression across all input values
sum(expressão)	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 expressão 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 may be used to substitute zero for null when necessary.

Nota: 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.

Nota: 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.

Tabela 9-38 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.

Tabela 9-38. 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(XY) - 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(expressão)	smallint, int, bigint, real, double precision, or numeric	double precision for floating-point arguments, otherwise numeric	historical alias for stddev_samp
stddev_pop(expressão)	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(expressão)	smallint, int, bigint, real, double precision, or numeric	double precision for floating-point arguments, otherwise numeric	sample standard deviation of the input values
variance(expressão)	smallint, int, bigint, real, double precision, or numeric	double precision for floating-point arguments, otherwise numeric	historical alias for var_samp
var_pop(expressão)	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(expressão)	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)

Anterior	Principal	Próxima
Array Functions and Operators	Acima	Subquery Expressions