this is an aggregate function. As such, it accepts a single operand which can wither be a scalar, in which case it will operate in turn on each item of the group, or an array or a vector in which case it will operate on each value in sequence and return the result (in practice, this process is delayed until the group is submitted for performance reasons)
User can choose to skip over NULL values or to include them in the computation with one of the modifiers SKIP NULLS to skip NULL values (the default) and KEEP NULLS to include them.
In the first case the result will still be NULL if all input values are NULL, and in the last case any NULL value will make the result NULL.
The other modifier tells whether the state used to compute the aggregate must be local (each group has its own independent state) or global (all groups share a single state). In general when using a GROUP-BY clause the former behavior is intended, and it is thus the default when an explicit GROUP-BY clause is present. Otherwise, the default is to use only one global state.
One can choose between those two with the modifier LOCALLY to force a group-wise state and GLOBALLY to force a global state.
This choice of the state lifespan is only meaningful when the operation is applied to a single scalar value, since the state required to compute the end result over a literal array or vector lives only as long as that computation.
COUNT …expr…
t sequence -> u32
If the counted expression is a boolean, count how many are true. Otherwise, is equivalent to SUM 1 .
| expression | evaluates to |
|---|---|
| COUNT [ 1; 1; 1 ] | 3 |
| COUNT [ TRUE ; FALSE ; TRUE ] | 2 |