Aggregate functions compute a single result from a set of input values. For example, you can compute the count, sum, average, maximum, and minimum over a set of rows.
SELECT MIN(public_year) FROM book;
min ------ 1869
To utilize an aggregate function in the
WHERE clause, use a subquery. The following example returns the title of the oldest book:
SELECT title FROM book WHERE public_year = (SELECT min(public_year) FROM book);
title --------------- War and Peace
You can also combine aggregate functions with the
GROUP BY clauses.
For example, we can get the oldest book of each genre:
SELECT genre, min(public_year) public_year FROM book GROUP BY genre;
genre | public_year -----------------+------------- science fiction | 1895 fantasy | 1937 novel | 1869
You can use the
HAVING clause to filter the result set:
SELECT genre, min(public_year) public_year FROM book GROUP BY genre HAVING min(public_year) < 1900;
genre | min -----------------+------ science fiction | 1895 novel | 1869
NOTEThere is a fundamental difference between the
If you need to use additional conditions, add them to the
To obtain the full list of predefined aggregate functions, refer to the following link: Aggregate functions.
The syntax of an aggregate expression can be one of the following:
The first form of aggregate expression invokes the aggregate function once for each input row according to the specified expression:
<aggregate_name> (<expression> [ , ... ] [<order_by_clause>] ) [ FILTER ( WHERE <filter_clause> ) ]
aggregate_name is an aggregate function;
expression is a value expression that does not contain any aggregate expression or window function call;
order_by_clause is a clause that specifies the sort order (optional);
filter_clause is a clause that defines a filter (optional).
SELECT MIN(public_year) FROM book;
The second form has the
ALLis used by default, the second form is the same as the first.
<aggregate_name> (ALL <expression> [ , ... ] [<order_by_clause>] ) [ FILTER (WHERE <filter_clause>) ]
The third form invokes the aggregate function once for each distinct value of the expression (or distinct set of values, for multiple expressions).
<aggregate_name> (DISTINCT <expression> [ , ... ] [<order_by_clause>] ) [ FILTER (WHERE <filter_clause>) ]
The following statement returns the number of distinct book titles:
SELECT COUNT (DISTINCT(book_title)) FROM orders;
The fourth form invokes the aggregate function once for each row. Since no particular input value is specified, this form only applies to the
<aggregate_name> ( * ) [ FILTER (WHERE <filter_clause>) ]
The fifth form is used with ordered-set aggregate functions (for example,
percentile). The result of these functions depends on the order of the input rows. The
order_by_clauseparameter is required for these functions.
<aggregate_name> ( [ <expression> [ , ... ] ] ) WITHIN GROUP ( <order_by_clause>) [ FILTER (WHERE <filter_clause>) ]
ORDER BYclause has the same syntax as a query-level
ORDER BYclause, but cannot contain output-column numbers and names.
SELECT array_agg(field1 ORDER BY field2 DESC) FROM table1;
If you call multiple-argument aggregate functions,
ORDER BYmust be placed after all aggregate arguments:
SELECT string_agg(field1, ',' ORDER BY field1) FROM table1;
DISTINCTis specified in addition to
ORDER BY, all the
ORDER BYexpressions must match regular arguments of the aggregate function. You cannot sort by an expression that is not included in the DISTINCT list.
For an ordered-set aggregate function, you can put
WITHIN GROUP(…). The expressions in
order_by_clauseare evaluated once per input row as regular aggregate arguments, sorted according to
order_by_clauserequirements, and passed to the aggregate function as input arguments. When
order_by_clauseis not in
WITHIN GROUP, it is not treated as an aggregate function argument. The argument expressions preceding
WITHIN GROUP, if any, are called direct arguments to distinguish them from the aggregated arguments listed in the
order_by_clause. Direct arguments are evaluated once per aggregate call. They can contain variables only if these variables are grouped by
GROUP BY. Direct arguments are typically used for a single value per aggregation calculation (for example, percentile fractions). If the direct argument list is empty, write
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;
For all types of aggregate expression listed above,
filter_clause is an optional parameter. If
FILTER is specified, only the input rows for which the
filter_clause evaluates to
true are passed to the aggregate function.
We have the following table:
id | book_title | public_year ----+-------------------------------------+------------- 1 | Mrs. Dalloway | 1925 2 | To the Lighthouse | 1927 3 | To Kill a Mockingbird | 1960 4 | The Great Gatsby | 1925 5 | The Lord of the Rings | 1955 8 | War and Peace | 1869 7 | The Hobbit, or There and Back Again | 1937 9 | Hyperion | 1989 10 | The Time Machine | 1895 6 | 1984 | 1949
Execute query with the specified
SELECT COUNT(*) AS unfiltered, COUNT(*) FILTER (WHERE public_year < 1900) AS filtered FROM book;
unfiltered | filtered ------------+---------- 10 | 2
Most aggregate functions ignore NULL input, and rows where one or more expressions evaluate to NULL are discarded. This is true for all built-in aggregate functions unless otherwise specified. For example,
COUNT(*) returns the total number of input rows,
COUNT(field1) returns the number of input rows where
field1 is not null, and
COUNT(DISTINCT field1) returns the number of distinct non-null values of