Aggregate Expressions

An aggregate expression applies an aggregate function across the rows or groups of rows selected by a query.

An aggregate expression only can appear in the select list or HAVING clause of a SELECT statement. It is invalid in other clauses such as WHERE, because those clauses are evaluated before the results of aggregates are formed.

Syntax

An aggregate expression has the following format:

aggregate‑function ( [ * ] [ ALL | DISTINCT ] expression )

Parameters

aggregate‑function A Vertica function that aggregates data over groups of rows from a query result set.
ALL | DISTINCT

Specifies which input rows to process:

  • ALL (default): Invokes aggregate‑function across all input rows where expression evaluates to a non-null value.
  • DISTINCT: Invokes aggregate‑function across all input rows where expression evaluates to a unique non-null value.
expression A value expression that does not itself contain an aggregate expression.