| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
COUNT_BIG (Transact-SQL) |
COUNT_BIG (Transact-SQL) |
markingmyname |
maghan |
07/24/2017 |
sql |
t-sql |
reference |
|
|
|
>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current||=fabric |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]
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.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
-- Aggregation Function Syntax
COUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } )
-- Analytic Function Syntax
COUNT_BIG ( [ ALL ] { expression | * } ) OVER ( [ <partition_by_clause> ] )
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.
bigint
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.
See COUNT (Transact-SQL) for examples.
Aggregate Functions (Transact-SQL)
COUNT (Transact-SQL)
int, bigint, smallint, and tinyint (Transact-SQL)
OVER Clause (Transact-SQL)