count_big

Returns the number of (distinct) non-null values, or the number of selected rows as a bigint.

Syntax

count_big([all | distinct] expression)

Parameters

Examples

Usage

  • When distinct is specified, count_big finds the number of unique non-null values. Null values are ignored when counting.

  • count_big(column_name) returns a value of 0 on empty tables, on columns that contain only null values, and on groups that contain only null values.

  • count_big(*) finds the number of rows. count_big(*) does not take any arguments, and cannot be used with distinct. All rows are counted, regardless of the presence of null values.

  • When tables are being joined, include count_big(*) in the select list to produce the count of the number of rows in the joined results. If the objective is to count the number of rows from one table that match criteria, use count_big(column_name).

  • You can use count_big as an existence check in a subquery. For example:
    select * from tab where 0 < 
        (select count_big(*) from tab2 where ...)
    However, because count_big counts all matching values, exists or in may return results faster. For example:
    select * from tab where exists
        (select * from tab2 where ...)

See also compute clause, group by and having clauses, select, where clause commands in Reference Manual: Commands

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute count_big.