An
aggregate function
is a special kind of function that operates on
several rows
of a query at once, returning a single result. Such functions are generally only used in queries which make
use of the GROUP BY clause to associate rows together by like criteria, though they may
be used in queries which only contain aggregate functions in their target list. When performing the latter, the
aggregate function operates on
all
selected rows from the result set.
Table 516 provides an overview of PostgreSQL's supported aggregate functions. To see a
complete list of aggregate functions, you may use the \da command within
psql
.
Table 516. Aggregate functions
Function

Description

avg(
expression
)

Returns the average of the
expression
values from all rows in a group.

count(
expression
)

Returns the number of values, per each aggregated group of rows, for which
expression
is not NULL

max(
expression
)

Returns the maximum value of
expression
in the grouped rows

min(
expression
)

Returns the minimum value of
expression
in the grouped rows

stddev(
expression
)

Returns the standard deviation of the values of
expression
in the grouped rows

sum(
expression
)

Returns the sum of the values of
expression
in the grouped rows

variance(
expression
)

Returns the variance of the values of
expression
in the grouped rows

The following sections describe each aggregate function in further detail, including specific information on
usage, examples, and valid input data types. In each of the functional explanations, the term
expression
refers to any valid identifier in a result set, or any valid expression operating on such
an identifier.
When calling an aggregate function,
aggregate expressions
are employed to describe an
expression from the result set created by the SELECT statement. An aggregate expression
is similar to an ordinary SQL expression, but may be preceded by either the ALL or the
DISTINCT keyword.
The use of the DISTINCT keyword in an aggregate expression causes only grouped
rows with unique values (as described by the expression) to be evaluated by the function. Any duplicate rows will be
suppressed. Similar to the use of the ALL keyword in a
SELECT statement, the use of ALL in an aggregate
expression has no function other than to make more explicit the request for all grouped rows to be evaluated to the
function. Example 519 demonstrates each of the aggregate expression forms.
Example 519. Using aggregate expressions
booktown=#
SELECT count(location) AS set_locations,
booktown#
count(ALL location) AS all_set_locations,
booktown#
count(DISTINCT location) AS unique_locations,
booktown#
count(*) AS all_rows
booktown#
FROM subjects;
set_locations  all_set_locations  unique_locations  all_rows
+++
15  15  7  16
(1 row)
There is one final form of aggregate expression, as demonstrated by the all_rows
result column in Example 519. When the asterisk (*)
symbol is supplied as the aggregate expression, it instructs the aggregate function to evaluate
all rows
,
including rows with values of NULL, which are ordinarily ignored. Since
the subjects table contains one row with a NULL value
in the location column, the counted rows for location
differ from those counted for *.
Warning

Rows whose evaluated aggregate expression contain NULL values will not be evaluated
by an aggregate function (with the exception of the count() function).

avg(
expression
)
The avg() function accepts an expression describing aggregated values that are
either of any numeric type (numeric, bigint,
smallint, real, or double precision),
or of the interval time type.
The average, or mean, of the values described by
expression
in the grouped rows is returned.
The resultant value is returned as a value of type numeric for expressions of type
integer and double precision for expressions of type
real. All other expression types cause a value of the same data type to be returned.
Examples
booktown=#
SELECT avg(cost) AS average_cost,
booktown#
avg(retail) AS average_price,
booktown#
avg(retail  cost) AS average_profit
booktown#
FROM stock;
average_cost  average_price  average_profit
++
24.8235294118  30.0088235294  5.1852941176
(1 row)
booktown=#
SELECT avg(cost) AS average_cost, p.name AS publisher
booktown#
FROM (stock JOIN editions USING (isbn))
booktown#
JOIN publishers AS p (publisher_id)
booktown#
USING (publisher_id)
booktown#
GROUP BY p.name;
average_cost  publisher
+
26.5000000000  Ace Books
19.0000000000  Books of Wonder
26.5000000000  Doubleday
25.0000000000  HarperCollins
18.0000000000  Henry Holt & Company, Inc.
23.0000000000  Kids Can Press
23.0000000000  Mojo Press
20.0000000000  Penguin
23.0000000000  Random House
26.5000000000  Roc
26.0000000000  WatsonGuptill Publications
(11 rows)
count(
expression
)
The count() function returns the number of values in a set of aggregated rows
where the
expression
is not NULL. The
count() is not restricted as to the data type described by
expression
. It is important to understand that the count() function
only counts values which are not NULL. As a result, it is important to use an
expression
whose value will not be returned NULL in order for the
expression
to be meaningful to the counted results.
You may pass the asterisk (*) character to
count() in order to simply count all rows in an aggregation (including rows with
NULL values).
Examples
booktown=#
SELECT count(*) FROM editions;
count

17
(1 row)
booktown=#
SELECT count(isbn), p.name
booktown#
FROM editions JOIN publishers AS p (publisher_id)
booktown#
USING (publisher_id)
booktown#
GROUP BY p.name
booktown#
ORDER BY count DESC;
count  name
+
3  Random House
2  Ace Books
2  Doubleday
2  Roc
1  Books of Wonder
1  HarperCollins
1  Henry Holt & Company, Inc.
1  Kids Can Press
1  Mojo Press
1  O'Reilly & Associates
1  Penguin
1  WatsonGuptill Publications
(12 rows)
max(
expression
)
The max() function returns the maximum found value described by
expression
in a set of aggregated rows. It accepts an
expression
that
may represent any numeric, string, date, or time data type. The maximum is returned as a value of the same
data type as the
expression
.
Examples
booktown=#
SELECT max(cost), max(retail) FROM stock;
max  max
+
36.00  46.95
(1 row)
booktown=#
SELECT max(retail), p.name
booktown#
FROM (stock NATURAL JOIN editions)
booktown#
JOIN publishers AS p (publisher_id)
booktown#
USING (publisher_id)
booktown#
GROUP BY p.name
booktown#
ORDER BY max DESC;
max  name
+
46.95  Roc
45.95  Ace Books
36.95  Doubleday
32.95  Random House
28.95  HarperCollins
28.95  WatsonGuptill Publications
24.95  Mojo Press
24.95  Penguin
23.95  Henry Holt & Company, Inc.
23.95  Kids Can Press
21.95  Books of Wonder
(11 rows)
min(
expression
)
The min() function returns the minimum found value described by
expression
in a set of aggregated rows. It accepts an
expression
which
may represent any numeric, string, date, or time data type. The minimum is returned as a value of the same
data type as the
expression
.
Examples
booktown=#
SELECT min(cost), min(retail) FROM stock;
min  min
+
16.00  16.95
(1 row)
booktown=#
SELECT min(retail), p.name
booktown#
FROM (stock NATURAL JOIN editions)
booktown#
JOIN publishers AS p (publisher_id)
booktown#
USING (publisher_id)
booktown#
GROUP BY p.name
booktown#
ORDER BY min ASC;
min  name
+
16.95  Random House
21.95  Ace Books
21.95  Books of Wonder
22.95  Roc
23.95  Henry Holt & Company, Inc.
23.95  Kids Can Press
24.95  Mojo Press
24.95  Penguin
28.95  Doubleday
28.95  HarperCollins
28.95  WatsonGuptill Publications
(11 rows)
stddev(
expression
)
The stddev() function accepts an expression describing values of any numeric type
(numeric, bigint,
smallint, real, or double precision),
and returns the standard deviation of the values within the aggregated rows. The resultant value is returned as double precision
for an expression describing floating point values, and numeric for all other types.
Examples
booktown=#
SELECT stddev(retail) FROM stock;
stddev

8.46
(1 row)
booktown=#
SELECT stddev(retail), p.name
booktown#
FROM (stock NATURAL JOIN editions)
booktown#
JOIN publishers AS p ON (publisher_id = p.id)
booktown#
GROUP BY p.name
booktown#
ORDER BY stddev DESC
booktown#
LIMIT 4;
stddev  name
+
16.97  Ace Books
16.97  Roc
8.02  Random House
5.66  Doubleday
(4 rows)
sum(
expression
)
The sum() function accepts an expression describing values of any numeric type
(numeric, bigint,
smallint, real, or double precision),
and returns the sum of the values within the aggregated rows. The returned value is of the type numeric
when operating on values of type integer and double precision when
operating on values of type real. The result is returned as the same data type as the values
described by
expression
for all other data types.
Examples
booktown=#
SELECT sum(stock) FROM stock
;
sum

508
(1 row)
booktown=#
SELECT sum(stock), s.subject
booktown#
FROM ((stock NATURAL JOIN editions)
booktown(#
JOIN books ON (books.id = book_id))
booktown#
JOIN subjects AS s
booktown#
ON (books.subject_id = s.id)
booktown#
GROUP BY s.subject
booktown#
ORDER BY sum DESC;
sum  subject
+
189  Horror
166  Science Fiction
91  Children's Books
28  Drama
18  Classics
16  Arts
(6 rows)
variance(
expression
)
The variance() function accepts an expression describing values of any numeric type
(numeric, bigint,
smallint, real, or double precision)
and returns the variance of the values within the aggregated rows. The variance is equivalent to the stddev() squared.
The resultant value is returned as double precision for an expression describing
floatingpoint values, and numeric for all other types.
Examples
booktown=#
SELECT variance(retail) FROM stock;
variance

71.60
(1 row)
booktown=#
SELECT variance(retail), p.name
booktown#
FROM (stock NATURAL JOIN editions)
booktown#
JOIN publishers AS p
booktown#
ON (editions.publisher_id = p.id)
booktown#
GROUP BY p.name
booktown#
ORDER BY variance DESC
booktown#
LIMIT 4;
variance  name
+
288.00  Ace Books
288.00  Roc
64.33  Random House
32.00  Doubleday
(4 rows)