COUNT function is used to count particular item in a group. It returns number of
items in a group.
Note
that :
COUNT(*) returns the number of
items in a group including NULL values and duplicates.
COUNT(ALL expression) evaluates expression for each row in a group and returns
the number of nonnull values.
COUNT(DISTINCT expression) evaluates expression for each row in a group and returns
the number of unique nonnull values.
Syntax of COUNT Function :
COUNT ( { [
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(*) takes no parameters and cannot
be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it
does not use information about any particular column. COUNT(*) 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 Funtion :
Example 1 : Using COUNT function in
SELECT clause to get total records in a table
SELECT COUNT(*)
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 function in
SELECT clause to get total product in invoices table
SELECT COUNT(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(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 nonnull values.