FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Built-in Scalar FunctionsWindow (Analytical) 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.

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.

General-purpose Aggregate Functions

AVG()

Average

Result type

Depends on the input type

Syntax
AVG ([ALL | DISTINCT] <expr>)
Table 1. AVG Function Parameters
Parameter Description

expr

Expression.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF that returns a numeric data type.Aggregate functions are not allowed as expressions

AVG returns the average argument value in the group.NULL is ignored.

  • Parameter ALL (the default) applies the aggregate function to all values.

  • Parameter DISTINCT directs the AVG function to consider only one instance of each unique value, no matter how many times this value occurs.

  • If the set of retrieved records is empty or contains only NULL, the result will be NULL.

The result type of AVG depends on the input type:

FLOAT, DOUBLE PRECISION

DOUBLE PRECISION

SMALLINT, INTEGER, BIGINT

BIGINT

INT128

INT128

DECIMAL/NUMERIC(p, n) with p < 19

DECIMAL/NUMERIC(18, n)

DECIMAL/NUMERIC(p, n) with p >= 19

DECIMAL/NUMERIC(38, n)

DECFLOAT(16)

DECFLOAT(16)

DECFLOAT(34)

DECFLOAT(34)