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.

Examples

The AVG aggregate function returns the average income from the customer_dimension table:

=> SELECT AVG(annual_income) FROM customer_dimension;
 AVG
--------------
 2104270.6485
(1 row)

The following example shows how to use the COUNT aggregate function with the DISTINCT keyword to return all distinct values of evaluating the expression x+y for all inventory_fact records.

=> SELECT COUNT (DISTINCT date_key + product_key) FROM inventory_fact;
COUNT
-------
21560
(1 row)