COUNT_BIG (-SQL)

**APPLIES TO:** SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

This function returns the number of items found in a group. COUNT_BIG operates like the COUNT function. These functions differ only in the data types of their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

Transact-SQL Syntax Conventions

Syntax

-- Aggregation Function Syntax  
COUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } )  
  
-- Analytic Function Syntax  
COUNT_BIG ( [ ALL ] { expression | * } ) OVER ( [ <partition_by_clause> ] )  

Arguments

ALL
Applies the aggregate function to all values. ALL serves as the default.

DISTINCT
Specifies that COUNT_BIG returns the number of unique nonnull values.

expression
An expression of any type. COUNT_BIG does not support aggregate functions or subqueries in an expression.

*
Specifies that COUNT_BIG should count all rows to determine the total table row count to return. COUNT_BIG(*) takes no parameters and does not support the use of 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, and it preserves duplicate rows. It counts each row separately, including rows that contain null values.

OVER ( [ partition_by_clause ] [ order_by_clause ] )
The partition_by_clause divides the result set produced by the FROM clause into partitions to which the COUNT_BIG function is applied. If not specified, the function treats all rows of the query result set as a single group. The order_by_clause determines the logical order of the operation. See OVER Clause (Transact-SQL) for more information.

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.

COUNT_BIG is a deterministic function when used without the OVER and ORDER BY clauses. COUNT_BIG is nondeterministic when used with the OVER and ORDER BY clauses. See Deterministic and Nondeterministic Functions for more information.

Examples

See COUNT (Transact-SQL) for examples.

See also

Aggregate Functions (Transact-SQL)
COUNT (Transact-SQL)
int, bigint, smallint, and tinyint (Transact-SQL)
OVER Clause (Transact-SQL)