Aggregate expressions

Aggregate expressions apply aggregate functions across the rows or groups of rows selected by a query.

Aggregate expressions apply aggregate functions 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

Aggregate expressions use the following format:

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

Parameters

aggregate-function An OpenText™ Analytics Database 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)