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 an single aggregate of an expression. For example SUM(x) + SUM(y) can be expressed as as SUM(x+y) (where x and y are NOT NULL).

  • Vertica does not support nested aggregate functions.

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