## 9.2. General-purpose Aggregate Functions

### 9.2.1. `AVG()`

Average

Result typeDepends on the input type

Syntax

```  |`AVG ([ALL | DISTINCT] <expr>)`
```

Table 9.1`AVG` Function Parameters
ParameterDescription

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)`

#### 9.2.1.1. `AVG` Examples

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

See also`SELECT`

### 9.2.2. `COUNT()`

Counts non-`NULL` values

Result type`BIGINT`

Syntax

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

Table 9.2`COUNT` Function Parameters
ParameterDescription

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

`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.

#### 9.2.2.1. `COUNT` Examples

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

### 9.2.3. `LIST()`

Concatenates values into a string list

Result type`BLOB`

Syntax

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

Table 9.3`LIST` Function Parameters
ParameterDescription

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

`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 `BLOB`s 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.

#### 9.2.3.1. `LIST` 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`

### 9.2.4. `MAX()`

Maximum

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

Syntax

```  |`MAX ([ALL | DISTINCT] <expr>)`
```

Table 9.4`MAX` Function Parameters
ParameterDescription

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.

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

• If the group is empty or contains only `NULL`s, 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 `BLOB`s of any size and character set.

Note

The `DISTINCT` parameter makes no sense if used with `MAX()` as it doesn’t change the result; it 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`
```

### 9.2.5. `MIN()`

Minimum

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

Syntax

```  |`MIN ([ALL | DISTINCT] <expr>)`
```

Table 9.5`MIN` Function Parameters
ParameterDescription

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.

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

• If the group is empty or contains only `NULL`s, 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 `BLOB`s of any size and character set.

Note

The `DISTINCT` parameter makes no sense if used with `MIN()` as it doesn’t change the result; it 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`
```

### 9.2.6. `SUM()`

Sum

Result typeDepends on the input type

Syntax

```  |`SUM ([ALL | DISTINCT] <expr>)`
```

Table 9.6`SUM` Function Parameters
ParameterDescription

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.

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

• If the group is empty or contains only `NULL`s, 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:

 `FLOAT, DOUBLE PRECISION` `DOUBLE PRECISION` `SMALLINT, INTEGER` `BIGINT` `BIGINT, INT128` `INT128` `DECIMAL/NUMERIC(p, n) with p < 10` `DECIMAL/NUMERIC(18, n)` `DECIMAL/NUMERIC(p, n) with p >= 10` `DECIMAL/NUMERIC(38, n)` `DECFLOAT(16), DECFLOAT(34)` `DECFLOAT(34)`

#### 9.2.6.1. `SUM` Examples

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

See also`SELECT`