COUNT

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks > Functions > Aggregate Functions >

COUNT

Previous pageReturn to chapter overviewNext page

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.

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.

If DISTINCT is specified, only distinct values of <value expression> are counted.

COUNT(DISTINCT) works both  the WINDOW and GROUP BY clauses of the SELECT statement.

Limitations

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.