1. SQL Reference Guide
  2. SQL Query Management
  3. SELECT Expression
  4. Using Aggregate Functions
  5. Sequence Aggregate Functions

Sequence Aggregate Functions

Sequence aggregate functions simply aggregate the items in their input sequence, using the same rules as their corresponding SQL aggregate function. Sequence aggregate functions can be used to aggregate the values of an array or map inside the current context row, without any grouping across rows.

For example, seq_sum() will skip any non-numeric items in the input sequence and it will determine the actual type of the return value (long, double, or number) the same way as the SQL sum(). The only exception is seq_count(), which contrary to the SQL count(), will return NULL if any of its input items is NULL. Furthermore, there are no restrictions on where sequence aggregate functions can appear (for example, they can be used in the WHERE and/or the SELECT clause). An example using seq_sum and seq_max is given in Example 6-30.

The following functions are the sequence aggregate functions.

  • long seq_count(any*)
  • number seq_sum(any*)
  • number seq_avg(any*)
  • any_atomic seq_min(any*)
  • any_atomic seq_max(any*)

Note:

All sequence aggregate function names are case sensitive.

long seq_count(any*)

Returns the number of items in the input sequence. See long count(any*) for details.

number seq_sum(any*)

Returns the sum of the numeric items in the input sequence. See number sum(any*) for details.

number seq_avg(any*)

Returns the average of the numeric items in the input sequence. See number avg(any*) for details.

any_atomic seq_min(any*)

Returns the minimum of the items in the input sequence. See any_atomic min(any*) for details.

any_atomic seq_max(any*)

Returns the minimum of the items in the input sequence. See any_atomic max(any*) for details.

Example 6-6 Sequence Aggregate Function

SELECT id, seq_sum(u.expenses.values()) FROM users u;