COUNT( [DISTINCT | ALL] <value-expression> ) [ OVER <window-specification> ]
Re DISTINCT, see Limitations, below.
COUNT ( * ) [OVER <window-specification> ]
COUNT ( * ) [OVER ( <window-specification )> ]
The COUNT function returns the number of qualifying rows in the aggregation. When used without the OVER clause, COUNT is considered an aggregate function:
COUNT( <value-expression> )
COUNT( ALL <value-expression> )
COUNT( DISTINCT <value-expression> )
When used with the OVER clause, it is an analytic function.
COUNT( <value-expression> ) OVER ( <window-specification> )
COUNT( ALL <value-expression> ) OVER ( <window-specification> )
In all the above forms of the COUNT function, only rows where the <value_expression> is not NULL are counted. If ALL is specified, all such rows are counted. Since ALL is assumed by default, the first two above are equivalent.
If DISTINCT is specified, only distinct values of <value expression> are counted.
When used as an analytic function, COUNT will return zero if the window being evaluated contains no rows. In the case of a PARTITION BY, COUNT will return zero if the partition within the window matching the input row contains no rows.
In the second form, COUNT(*), all rows qualify.
SQLstream s-Server does not support the FILTER clause of the COUNT function, nor the use of COUNT DISTINCT in either streaming aggregation, windowed aggregation, or as an analytic function. These are departures from the SQL:2008 standard.