COUNT()
Counts non-NULL
values
BIGINT
COUNT ([ALL | DISTINCT] <expr> | *)
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 |
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.
COUNT
ExamplesSELECT
dept_no,
COUNT(*) AS cnt,
COUNT(DISTINCT name) AS cnt_name
FROM employee
GROUP BY dept_no
LIST()
Concatenates values into a string list
BLOB
LIST ([ALL | DISTINCT] <expr> [, separator ])
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 |
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. |