|PostgreSQL Reference Manual - Volume 1 - SQL Language Reference|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 716 pages
RRP £32.00 ($49.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
2.2.7 Aggregate Expressions
An aggregate expression represents the application of an aggregate function across the rows selected by a query. An aggregate function reduces multiple inputs to a single output value, such as the sum or average of the inputs. The syntax of an aggregate expression is one of the following:
aggregate_name (expression [ , ... ] ) aggregate_name (ALL expression [ , ... ] ) aggregate_name (DISTINCT expression [ , ... ] ) aggregate_name ( * )
where aggregate_name is a previously defined aggregate (possibly qualified with a schema name), and expression is any value expression that does not itself contain an aggregate expression.
The first form of aggregate expression invokes the aggregate
across all input rows for which the given expression(s) yield
non-null values. (Actually, it is up to the aggregate function
whether to ignore null values or not--but all the standard ones do.)
The second form is the same as the first, since
ALL is the default. The third form invokes the
aggregate for all distinct non-null values of the expressions found
in the input rows. The last form invokes the aggregate once for
each input row regardless of null or non-null values; since no
particular input value is specified, it is generally only useful
count(*) aggregate function.
count(*) yields the total number
of input rows;
count(f1) yields the number of
input rows in which
f1 is non-null;
count(distinct f1) yields the number of
distinct non-null values of
The predefined aggregate functions are described in section 7.15 Aggregate Functions. Other aggregate functions may be added by the user.
An aggregate expression may only appear in the result list or
HAVING clause of a
It is forbidden in other clauses, such as
because those clauses are logically evaluated before the results
of aggregates are formed.
When an aggregate expression appears in a subquery (see
section 2.2.9 Scalar Subqueries and
section 7.16 Subquery Expressions), the aggregate is normally
evaluated over the rows of the subquery. But an exception occurs
if the aggregate's arguments contain only outer-level variables:
the aggregate then belongs to the nearest such outer level, and is
evaluated over the rows of that query. The aggregate expression
as a whole is then an outer reference for the subquery it appears in,
and acts as a constant over any one evaluation of that subquery.
The restriction about
appearing only in the result list or
applies with respect to the query level that the aggregate belongs to.
Note: PostgreSQL currently does not support
DISTINCTwith more than one input expression.
|ISBN 0954612027||PostgreSQL Reference Manual - Volume 1 - SQL Language Reference||See the print edition|