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

Concatenates values into a string list

Result typeBLOB

Syntax

  |LIST ([ALL | DISTINCT] <expr> [, separator ])

LIST returns a string consisting of the non-NULL argument values in the group, separated either by a comma or by a user-supplied separator. If there are no non-NULL values (this includes the case where the group is empty), NULL is returned.

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

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

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

• Datetime and numeric arguments are implicitly converted to strings before concatenation.

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

• The ordering of the list values is undefined — the order in which the strings are concatenated is determined by read order from the source set which, in tables, is not generally defined. If ordering is important, the source data can be pre-sorted using a derived table or similar.

  ⚠

  Caution

  This is a trick/workaround, and it depends on implementation details of the optimizer/execution order. This trick doesn’t always work, and it is not guaranteed to work across versions.

  Some reports indicate this no longer works in Firebird 5.0, or only in more limited circumstances than in previous versions.

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