|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.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 [ , ... ] [ order_by_clause ] ) aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) aggregate_name ( * )
where aggregate_name is a previously
defined aggregate (possibly qualified with a schema name),
any value expression that does not itself contain an aggregate
expression or a window function call, and
order_by_clause is a optional
ORDER BY clause as described below.
The first form of aggregate expression invokes the aggregate
once for each input row.
The second form is the same as the first, since
ALL is the default.
The third form invokes the aggregate once for each distinct value
of the expression (or distinct set of values, for multiple expressions)
found in the input rows.
The last form invokes the aggregate once for each input row; since no
particular input value is specified, it is generally only useful
count(*) aggregate function.
Most aggregate functions ignore null inputs, so that rows in which one or more of the expression(s) yield null are discarded. This can be assumed to be true, unless otherwise specified, for all built-in aggregates.
count(*) yields the total number
of input rows;
count(f1) yields the number of
input rows in which
f1 is non-null, since
count ignores nulls; and
count(distinct f1) yields the number of
distinct non-null values of
Ordinarily, the input rows are fed to the aggregate function in an
unspecified order. In many cases this does not matter; for example,
min produces the same result no matter what order it
receives the inputs in. However, some aggregate functions
results that depend on the ordering of the input rows. When using
such an aggregate, the optional order_by_clause can be
used to specify the desired ordering. The order_by_clause
has the same syntax as for a query-level
ORDER BY clause, as
described in section 5.5 Sorting Rows, except that its expressions
are always just expressions and cannot be output-column names or numbers.
SELECT array_agg(a ORDER BY b DESC) FROM table;
When dealing with multiple-argument aggregate functions, note that the
ORDER BY clause goes after all the aggregate arguments.
For example, write this:
SELECT string_agg(a, ',' ORDER BY a) FROM table;
SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
The latter is syntactically valid, but it represents a call of a
single-argument aggregate function with two
ORDER BY keys
(the second one being rather useless since it's a constant).
DISTINCT is specified in addition to an
order_by_clause, then all the
expressions must match regular arguments of the aggregate; that is,
you cannot sort on an expression that is not included in the
Note: The ability to specify both
ORDER BYin an aggregate function is a PostgreSQL extension.
The predefined aggregate functions are described in section 7.18 Aggregate Functions. Other aggregate functions can be added by the user.
An aggregate expression can 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.10 Scalar Subqueries and
section 7.20 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.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|