count-big-transact-sql.md

title	COUNT_BIG (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	07/24/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	COUNT_BIG_TSQL COUNT_BIG
dev_langs	TSQL
helpviewer_keywords	totals [SQL Server], COUNT_BIG function counting items in group groups [SQL Server], number of items in number of group items COUNT_BIG function
ms.assetid	f2e3601f-487e-4917-bb01-47b1047908cd
caps.latest.revision	44
author	BYHAM
ms.author	rickbyh
manager	jhubbard

COUNT_BIG (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

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.

Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )  
   [ OVER ( [ partition_by_clause ] [ order_by_clause ] ) ]  

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  

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

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.

ALL
Applies the aggregate function to all values. ALL is the default.

DISTINCT
Specifies that AVG be performed only on each unique instance of a value, regardless of how many times the value occurs.

expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.

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

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. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

Examples

For examples, see COUNT (Transact-SQL).

See also

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