COUNT_BIG
function is used to count particular item in a group. It returns number of
items in a group. It works same as COUNT function. The difference between COUNT
and COUNT_BIG function is COUNT function returns integer and COUNT_BIG function
returns big integer.
Note
that :
COUNT_BIG(*) returns the number
of items in a group including 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.
Syntax of COUNT Function :
COUNT_BIG( { [
ALL | DISTINCT ] expression ] | * } )
ALL is the default. It means all
values to be count.
DISTINCT means COUNT returns the
number of unique nonnull values.
Expression is an expression of any type except uniqueidentifier, text, image, or ntext. Aggregate functions and
subqueries are not permitted.
* means 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 eliminating duplicates.
It counts each row separately, including rows that contain null values.
It returns an integer.
Examples of COUNT_BIG Funtion :
Example 1 : Using COUNT_BIG
function in SELECT clause to get total records in a table
SELECT COUNT_BIG(*)
FROM Products
Output
76
Above example displays the total
number of records in a product table using count function. It considers all
values in a group including null and duplicates.
Example 2 : Using COUNT_BIG function
in SELECT clause to get total product in invoices table
SELECT COUNT_BIG(ProductName)
FROM Invoices
Output
2155
Above example displays the total
number of product in a invoices table using count function. It considers all
values in a group including duplicate values.
Example 3 : Using COUNT function
and DISTINCT in SELECT clause to get unique products in invoices table
SELECT COUNT_BIG(DISTINCT ProductName)
FROM Invoices
Output
77
Above example displays the total
number of unique product in a invoices table using count function. It considers
all values in a group which has unique values.