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.
An aggregate expression has the following format:
aggregate‑function ( [ * ] [ ALL | DISTINCT ] expression )
|aggregate‑function||A Vertica function that aggregates data over groups of rows from a query result set.|
Specifies which input rows to process:
|expression||A value expression that does not itself contain an aggregate expression.|
The AVG aggregate function returns the average income from the customer_dimension table:
=> SELECT AVG(annual_income) FROM customer_dimension;
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;