Chapter 9. Aggregate Functions

Aggregate functions operate on groups of records, rather than on individual records or variables. They are often used in combination with a GROUP BY clause.

Syntax 

  |<aggregate_function> ::=
  |    aggragate_function ([<expr> [, <expr> ...]])
  |      [FILTER (WHERE <condition>)]

The aggregate functions can also be used as window functions with the OVER () clause. See Window (Analytical) Functions for more information.

Aggregate functions are available in DSQL and PSQL. Availability in ESQL is not tracked by this Language Reference.

9.1. FILTER Clause for Aggregate Functions

The FILTER clause extends aggregate functions (SUM, AVG, COUNT, etc.) with an additional WHERE clause. This limits the rows processed by the aggregate functions to the rows that satisfy the conditions of both the main WHERE clause and those inside the FILTER clause.

It can be thought of as a more explicit form of using an aggregate function with a condition (DECODE, CASE, IIF, NULLIF) to ignore some values that would otherwise be considered by the aggregation.

The FILTER clause can be used with any aggregate functions in aggregate or windowed (OVER) statements, but not with window-only functions like DENSE_RANK.

Example of FILTER

Suppose you need a query to count the rows with status = 'A' and the row with status = 'E' as different columns. The old way to do it would be:

  |select count(decode(status, 'A', 1)) status_a,
  |       count(decode(status, 'E', 1)) status_e
  |from data;

The FILTER clause lets you express those conditions more explicitly:

  |select count(*) filter (where status = 'A') status_a,
  |       count(*) filter (where status = 'E') status_e
  |from data;
☞
Tip

You can use more than one FILTER modifier in an aggregate query. You could, for example, use 12 filters on totals aggregating sales for a year to produce monthly figures for a pivot set.