1 out of 2 rated this helpful - Rate this topic

COUNT_BIG (Transact-SQL)

SQL Server 2008 R2

Returns the number of items in a group. COUNT_BIG works like the COUNT function. The only difference between the two functions is their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value. May be followed by the OVER Clause (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

Syntax


COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )

Arguments

ALL: Applies the aggregate function to all values. ALL is the default.
DISTINCT: Specifies that COUNT_BIG returns the number of unique nonnull values.
expression: Is an expression of any type. Aggregate functions and subqueries are not permitted.
*: Specifies that all rows should be counted to return the total number of rows in a table. COUNT_BIG(*) takes no parameters and cannot be used with DISTINCT. COUNT_BIG(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT_BIG(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values.

Return Types

bigint

Remarks

COUNT_BIG(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT_BIG(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT_BIG(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

Reference

Aggregate Functions (Transact-SQL)

COUNT (Transact-SQL)

int, bigint, smallint, and tinyint (Transact-SQL)

OVER Clause (Transact-SQL)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD