Firebird 2.5 Language Reference → Firebird 2.5 Language Reference → Built-in functions and Variables → Aggregate Functions
Firebird Firebird Prev: Scalar FunctionsFirebird 2.5 Language ReferenceUp: Built-in functions and VariablesNext: Transaction Control

Aggregate Functions

Table of Contents

AVG()
COUNT()
LIST()
MAX()
MIN()
SUM()

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.

AVG()

Available in: DSQL, ESQL, PSQL

Syntax: 

AVG ([ALL | DISTINCT] <expr>)

Table 8.69. 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


Description: 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.

Result type: A numeric data type, the same as the data type of the argument.

Syntax: 

AVG (expression)

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

Example: 

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

See also:  SELECT

COUNT()

Available in: DSQL, ESQL, PSQL

Syntax: 

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

Table 8.70. COUNT 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


Result type: Integer

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

  • ALL is the default: it simply counts all values in the set that are not NULL.
  • If DISTINCT is specified, duplicates are exluded 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.

Example: 

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

See also:  SELECT.

LIST()

Available in: DSQL, PSQL

Changed in: 2.5

Syntax: 

LIST ([ALL | DISTINCT] expression [, separator])

Table 8.71. LIST 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 the string data type or a BLOB. Fields of numeric and date/time types are converted to strings. Aggregate functions are not allowed as expressions
separator Optional alternative separator, a string expression. Comma is the default separator


Result type: BLOB

Description: 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 expression is a BLOB.

  • In Firebird 2.5 and up, the optional separator argument may be any string expression. This makes it possible to specify e.g. ascii_char(13) as a separator. (This improvement has also been backported to 2.1.4.)

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

  • Date/time and numeric arguments are implicitly converted to strings before concatenation.

  • The result is a text BLOB, except when expression 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.

Examples: 

  1. Retrieving the list, order undefined: 
    SELECT LIST (display_name, '; ') FROM GR_WORK;
  2. Retrieving the list in alphabetical order, using a derived table: 
    SELECT LIST (display_name, '; ')
FROM (SELECT display_name
      FROM GR_WORK
      ORDER BY display_name);

See also:  SELECT

MAX()

Available in: DSQL, ESQL, PSQL

Syntax: 

MAX ([ALL | DISTINCT] <expr>)

Table 8.72. MAX 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. Aggregate functions are not allowed as expressions.


Result type: Returns a result of the same data type the input expression.

Description: 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.

[Note] Note

The DISTINCT parameter makes no sense if used with MAX() and is implemented only for compliance with the standard.

Example: 

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

See also:  MIN, SELECT

MIN()

Available in: DSQL, ESQL, PSQL

Syntax: 

MIN ([ALL | DISTINCT] <expr>)

Table 8.73. MIN 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. Aggregate functions are not allowed as expressions.


Result type: Returns a result of the same data type the input expression.

Description: 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.

[Note] Note

The DISTINCT parameter makes no sense if used with MIN() and is implemented only for compliance with the standard.

Example: 

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

See also:  MAX, SELECT

SUM()

Available in: DSQL, ESQL, PSQL

Syntax: 

SUM ([ALL | DISTINCT] <expr>)

Table 8.74. SUM Function Parameters

Parameter Description
expr Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.


Result type: Returns a result of the same numeric data type as the input expression.

Description: 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 afterwards.

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

See also:  SELECT

Prev: Scalar FunctionsFirebird 2.5 Language ReferenceUp: Built-in functions and VariablesNext: Transaction Control
Firebird 2.5 Language Reference → Firebird 2.5 Language Reference → Built-in functions and Variables → Aggregate Functions