COUNT_BIG: Counting the Number of Occurrences

COUNT_BIG counts the number of rows in a specified field, expression, or table. The value is returned as a BIGINT data type.

Count the Number of Occurrences in a Field, Expression, or Table

COUNT_BIG(*)

COUNT_BIG(field) OVER([PARTITION BY part1[, part2, ...]]

    ORDER BY ord1[, ord2, ...]  [window])

where:

*

Returns the count of all rows in a table, including NULL rows.

field

Is the name of the input field or expression.

PARTITION BY part1, part2

Are partitioning fields or expressions.

ORDER BY ord1, ord2

Specifies the row order within each partition. The sort order can affect the result, as it changes the rows that are included in the sliding window on which the calculation is performed.

window

Defines the sliding window within each partition (starting row and ending row for the window). The window clause defines a frame around the current row within a partition over which the analytic function is evaluated. Both physical window frames (defined by ROWS) and logical window frames (defined by RANGE) are allowed.

The basic syntax for the window frame clause is:

{ROWS|RANGE} {{UNBOUNDED PRECEDING|numeric_expression PRECEDING|CURRENT ROW} | {BETWEEN boundary_start AND boundary_end}}

The basic syntax for the start of the boundary is:

{UNBOUNDED PRECEDING|numeric_expression PRECEDING|CURRENT ROW}

The basic syntax for the end of the boundary is:

{UNBOUNDED FOLLOWING|numeric_expression {PRECEDING|FOLLOWING}| CURRENT ROW

Counting the Number of Occurrences in a Field

The following syntax counts the number of occurrences of Quantity in Stock, returned as a BIGINT data type.

COUNT_BIG(QTY_IN_STOCK)