Aggregate functions

All functions in this section that have an analytic function counterpart are appended with [Aggregate] to avoid confusion between the two.

Aggregate functions summarize data over groups of rows from a query result set. The groups are specified using the GROUP BY clause. They are allowed only in the select list and in the HAVING and ORDER BY clauses of a SELECT statement (as described in Aggregate expressions).

Except for COUNT, these functions return a null value when no rows are selected. In particular, SUM of no rows returns NULL, not zero.

In some cases, you can replace an expression that includes multiple aggregates with a single aggregate of an expression. For example SUM(x) + SUM(y) can be expressed as as SUM(x+y) if neither argument is NULL.

Vertica does not support nested aggregate functions.

You can use some of the simple aggregate functions as analytic (window) functions. See Analytic functions for details. See also SQL analytics.

Some collection functions also behave as aggregate functions.

In this section