COUNT

The COUNT function returns the number of qualifying rows in the aggregation. When used without the OVER clause, COUNT is considered an aggregate function. When used with the OVER clause, it is an analytic function.

Syntax

COUNT ( [DISTINCT | ALL] <value-expression> ) [ OVER <window-specification> ]

COUNT is the count of all non-null values for . The DISTINCT qualifier eliminates duplicates. The ALL qualifier retains duplicates.

ALL is assumed if neither ALL nor DISTINCT is specified. For example, if a column named col contains the values 1,2,2,2,3, COUNT(col) or the equivalent expression COUNT(ALL col) returns 5; COUNT(DISTINCT col) returns 3. COUNT(DISTINCT) works with both the WINDOW and GROUP BY clauses of the SELECT statement.

If the value of COUNT() is null, COUNT returns zero.

You can use COUNT(*) to count rows. COUNT(DISTINCT *) is not meaningful. When used as an analytic function, COUNT will return zero if the window being evaluated contains no rows if the window being evaluated (or in the case of a PARTITION BY, the partition within the window matching the input row) contains no rows, as in the case of an offset window that contains no rows prior to CURRENT ROW. For more information on offset windows, see the topic WINDOW clause in this guide.

When used as an analytic function, COUNT will return zero if the window being evaluated contains no rows if the window being evaluated (or in the case of a PARTITION BY, the partition within the window matching the input row) contains no rows, as in the case of an offset window that contains no rows prior to CURRENT ROW. For more information on offset windows, see the topic WINDOW clause in this guide.

Examples

Given the following rows: |

ROWTIME ‘AMOUNT’ ‘TICKER’
‘2019-03-30 03:02:00.000’ ‘20’ ‘ORCL ‘
‘2019-03-30 03:02:10.000’ ‘20’ ‘ORCL’
‘2019-03-30 03:03:00.000’ ‘30’ ‘IBM ‘
‘2019-03-30 03:04:00.000’ ‘15’ ‘ORCL ‘
‘2019-03-30 03:04:30.000’ ‘40’ ‘IBM ‘

and the following code:

SELECT STREAM ROWTIME,
COUNT(DISTINCT ticker) OVER win AS count_ticker_distinct
FROM ticker_rt AS s
WINDOW win
AS (RANGE INTERVAL '1' HOUR PRECEDING)
;

COUNT(DISTINCT ticker) returns the following:

ROWTIME COUNT_TICKER_DISTINCT
2019-03-30 03:02:00.0 1
2019-03-30 03:02:10.0 1
2019-03-30 03:03:00.0 2
2019-03-30 03:04:00.0 2
2019-03-30 03:04:30.0 2

In the first and second rows, only one distinct value–‘ORCL’ exists for ticker. At the third row, two distinct values exist–‘ORCL’ and ‘IBM’. Because the fourth and fifth rows each repeat ‘ORCL’ or ‘IBM’, the value of COUNT(DISTINCT ticker) does not increase.

If you were to substitute COUNT(*) OVER win AS count_rows for COUNT(DISTINCT ticker) OVER win AS count_ticker_distinct, you would get the following result:

COUNT_ROWS
1
2
3
4
5

Limitations

SQLstream s-Server does not support the FILTER clause of the COUNT function.