COUNT OVER

Count the number of values within the requested window. For more information on usage, please refer to Window Functions

Syntax

COUNT( <value> ) OVER ( [ PARTITION BY <partition_by> ] )

Parameters

Parameter	Description	Supported input types
`<value>`	A value used for the `COUNT()` function.	Any numeric type
`<partition_by>`	An expression used for the `PARTITION BY` clause.	Any

Return Type

NUMERIC

Example

The following example counts how many players registered on each day by using COUNT as a window function partitioned by registration date.

SELECT
    registeredon,
    agecategory,
    COUNT(agecategory) OVER (PARTITION BY registeredon) AS count_of_players
FROM
    (VALUES
        ('2020-11-15'::DATE, 'Junior'),
        ('2020-11-15'::DATE, 'Senior'),
        ('2020-11-15'::DATE, 'Adult'),
        ('2020-11-16'::DATE, 'Junior'),
        ('2020-11-16'::DATE, 'Adult')
    ) AS t(registeredon, agecategory)
ORDER BY registeredon, agecategory;

registeredon date	agecategory text	count_of_players long
2020-11-15	Adult	3
2020-11-15	Junior	3
2020-11-15	Senior	3
2020-11-16	Adult	2
2020-11-16	Junior	2

Rows: 5Execution time: 2.39ms

AVG OVER

DENSE_RANK OVER

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Example

​Syntax

​Parameters

​Return Type

​Example

Syntax

Parameters

Return Type

Example