COUNT_BIG (Transact-SQL)

Article
12/29/2022
2 minutes to read

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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> ] )

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

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.