9.2 Generalpurpose Aggregate Functions
9.2.1 AVG()
Available inDSQL, ESQL, PSQL
Result typeDepends on the input type
Syntax
AVG ([ALL  DISTINCT] <expr>)
AVG
Function ParametersParameter  Description 

expr  Expression. It may contain a table column, a constant, a variable, an expression, a nonaggregate 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 theAVG
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 beNULL
.
The result type of AVG
depends on the input type:














In Firebird 4.0.0, the result type is determined the same as Section 9.2.6, SUM()
.
This was fixed in 4.0.1, see firebird#6845.
9.2.1.1 AVG
Examples
SELECT
dept_no,
AVG(salary)
FROM employee
GROUP BY dept_no
See alsoSELECT
9.2.2 COUNT()
Available inDSQL, ESQL, PSQL
Result typeBIGINT
Syntax
COUNT ([ALL  DISTINCT] <expr>  *)
COUNT
Function ParametersParameter  Description 

expr  Expression. It may contain a table column, a constant, a variable, an expression, a nonaggregate function or a UDF that returns a numeric data type. Aggregate functions are not allowed as expressions 
COUNT
returns the number of nonnull values in a group.
ALL
is the default: it simply counts all values in the set that are notNULL
.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 columnunspecific 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.
9.2.2.1 COUNT
Examples
SELECT
dept_no,
COUNT(*) AS cnt,
COUNT(DISTINCT name) AS cnt_name
FROM employee
GROUP BY dept_no
See alsoSELECT
.
9.2.3 LIST()
Available inDSQL, PSQL
Result typeBLOB
Syntax
LIST ([ALL  DISTINCT] <expr> [, separator ])
LIST
Function ParametersParameter  Description 

expr  Expression.
It may contain a table column, a constant, a variable, an expression, a nonaggregate function or a UDF that returns the string data type or a 
separator  Optional alternative separator, a string expression. Comma is the default separator 
LIST
returns a string consisting of the nonNULL
argument values in the group, separated either by a comma or by a usersupplied separator.
If there are no nonNULL
values (this includes the case where the group is empty), NULL
is returned.
ALL
(the default) results in all nonNULL
values being listed. WithDISTINCT
, duplicates are removed, except if expr is aBLOB
.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 expr and separator arguments support
BLOB
s 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 expr is aBLOB
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 presorted using a derived table or similar.
⚠︎CautionThis 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.
9.2.3.1 LIST
Examples
Retrieving the list, order undefined:
SELECT LIST (display_name, '; ') FROM GR_WORK;
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 alsoSELECT
9.2.4 MAX()
Available inDSQL, ESQL, PSQL
Result typeReturns a result of the same data type the input expression.
Syntax
MAX ([ALL  DISTINCT] <expr>)
MAX
Function ParametersParameter  Description 

expr  Expression. It may contain a table column, a constant, a variable, an expression, a nonaggregate function or a UDF. Aggregate functions are not allowed as expressions. 
MAX
returns the maximum nonNULL
element in the result set.
If the group is empty or contains only
NULL
s, the result isNULL
.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
BLOB
s of any size and character set.
The DISTINCT
parameter makes no sense if used with MAX()
and is implemented only for compliance with the standard.
9.2.4.1 MAX
Examples
SELECT
dept_no,
MAX(salary)
FROM employee
GROUP BY dept_no
See alsoSection 9.2.5, MIN()
, SELECT
9.2.5 MIN()
Available inDSQL, ESQL, PSQL
Result typeReturns a result of the same data type the input expression.
Syntax
MIN ([ALL  DISTINCT] <expr>)
MIN
Function ParametersParameter  Description 

expr  Expression. It may contain a table column, a constant, a variable, an expression, a nonaggregate function or a UDF. Aggregate functions are not allowed as expressions. 
MIN
returns the minimum nonNULL
element in the result set.
If the group is empty or contains only
NULL
s, the result isNULL
.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
BLOB
s of any size and character set.
The DISTINCT
parameter makes no sense if used with MIN()
and is implemented only for compliance with the standard.
9.2.5.1 MIN
Examples
SELECT
dept_no,
MIN(salary)
FROM employee
GROUP BY dept_no
See alsoSection 9.2.4, MAX()
, SELECT
9.2.6 SUM()
Available inDSQL, ESQL, PSQL
Result typeDepends on the input type
Syntax
SUM ([ALL  DISTINCT] <expr>)
SUM
Function ParametersParameter  Description 

expr  Numeric expression. It may contain a table column, a constant, a variable, an expression, a nonaggregate function or a UDF. Aggregate functions are not allowed as expressions. 
SUM
calculates and returns the sum of nonnull values in the group.
If the group is empty or contains only
NULL
s, the result isNULL
.ALL is the default option — all values in the set that are not
NULL
are processed. IfDISTINCT
is specified, duplicates are removed from the set and theSUM
evaluation is done afterwards.
The result type of SUM
depends on the input type:












9.2.6.1 SUM
Examples
SELECT
dept_no,
SUM (salary),
FROM employee
GROUP BY dept_no
See alsoSELECT