Returns the expression for an arbitrary row in the group.

Result typeDepends on the input type

Syntax

ANY_VALUE ( <expr> )

This is a non-deterministic aggregate function that returns the result of expr applied to an arbitrary row in the group.

If expr returns NULL for a row, another row is tried. NULL is only returned if expr evaluates to NULL for all rows in the group or the group is empty.

select
  DEPT_NO,
  any_value(FIRST_NAME || ' ' || LAST_NAME) "Example Employee"
from EMPLOYEE
group by DEPT_NO

Average

Result typeDepends on the input type

Syntax

AVG ([ALL | DISTINCT] <expr>)

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:

SELECT
  dept_no,
  AVG(salary)
FROM employee
GROUP BY dept_no

Counts non-NULL values

Result typeBIGINT

Syntax

COUNT ([ALL | DISTINCT] <expr> | *)

COUNT returns the number of non-null values in a group.

• ALL is the default: it counts all values in the set that are not NULL.

• If DISTINCT is specified, duplicates are excluded from the counted set.

• If COUNT (*) is specified instead of the expression expr, all rows will be counted. COUNT (*) — 

  • does not accept parameters

  • cannot be used with the keyword DISTINCT

  • does not take an expr argument, since its context is column-unspecific by definition

  • counts each row separately and returns the number of rows in the specified table or group without omitting duplicate rows

  • counts rows containing NULL

• If the result set is empty or contains only NULL in the specified column(s), the returned count is zero.

SELECT
  dept_no,
  COUNT(*) AS cnt,
  COUNT(DISTINCT name) AS cnt_name
FROM employee
GROUP BY dept_no

Since Firebird 6.0, LIST() is an alias for the SQL standard function Section 9.3.5, “LISTAGG()”.

Concatenates values into a string list

Result typeBLOB

Syntax

<listagg-set-function> ::=
  { LISTAGG | LIST }
    <left-paren>
      [ ALL | DISTINCT ] <expr>
      [ <comma> listagg-separator ]
      [ <listagg-overflow-clause> ]
    <right-paren>
    [ <within-group-specification> ]
 
<listagg-overflow-clause> ::=
  ON OVERFLOW <overflow-behavior>
 
<overflow-behavior> ::=
    ERROR
  | TRUNCATE [ listagg-truncation-filler ] <listagg-count-indication>
 
<listagg-count-indication> ::=
  WITH COUNT | WITHOUT COUNT
 
<within-group-specification> ::=
  !! See Section 9.2, “WITHIN GROUP Clause for Aggregate Functions” !!

LISTAGG returns a string consisting of the non-NULL argument values in the group, separated either by a comma or by listagg-separator. If there are no non-NULL values or if the group is empty, NULL is returned.

LISTAGG is defined by the SQL standard. For backward compatibility with Firebird 5.0 and older, LIST is an alias for LISTAGG. Compared to the SQL standard, some clauses and arguments are optional so LISTAGG and LIST can share the same definition.

• ALL (the default) results in all non-NULL values being listed. With DISTINCT, duplicates are removed, except if expr is a BLOB.

• The optional listagg-separator argument may be any expression convertible to string. This makes it possible to specify e.g. ascii_char(13) as a separator.

• The expr and listagg-separator arguments support BLOBs of any size and character set.

• Non-string arguments are implicitly converted to strings before concatenation.

• The result is a text BLOB, except when expr is a BLOB of another subtype.

• The order of concatenation of expr is only defined when the WITHIN GROUP clause is specified. Otherwise, the order is undefined and depends on implementation details of the optimizer and the selected execution plan.

  ☞

  Tip

  In Firebird 5.0 and older, ordering the concatenation performed by LIST was — sometimes — possible by using an ordered derived table. We highly recommend replacing such brittle workarounds with the WITHIN GROUP clause.

Maximum

Result typeReturns a result of the same data type the input expression.

Syntax

MAX ([ALL | DISTINCT] <expr>)

MAX returns the maximum non-NULL element in the result set.

• If the group is empty or contains only NULLs, the result is NULL.

• If the input argument is a string, the function will return the value that will be sorted last if COLLATE is used.

• This function fully supports text BLOBs of any size and character set.

SELECT
  dept_no,
  MAX(salary)
FROM employee
GROUP BY dept_no

Minimum

Result typeReturns a result of the same data type the input expression.

Syntax

MIN ([ALL | DISTINCT] <expr>)

MIN returns the minimum non-NULL element in the result set.

• If the group is empty or contains only NULLs, the result is NULL.

• If the input argument is a string, the function will return the value that will be sorted first if COLLATE is used.

• This function fully supports text BLOBs of any size and character set.

SELECT
  dept_no,
  MIN(salary)
FROM employee
GROUP BY dept_no

Sum

Result typeDepends on the input type

Syntax

SUM ([ALL | DISTINCT] <expr>)

SUM calculates and returns the sum of non-NULL values in the group.

• If the group is empty or contains only NULLs, the result is NULL.

• ALL is the default option — all values in the set that are not NULL are processed. If DISTINCT is specified, duplicates are removed from the set and the SUM evaluation is done afterward.

The result type of SUM depends on the input type:

SELECT
  dept_no,
  SUM (salary),
FROM employee
GROUP BY dept_no