Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value. May be followed by the OVER clause.
Transact-SQL Syntax Conventions
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
-
ALL
-
Applies the aggregate function to all values. ALL is the default.
- DISTINCT
-
Specifies that COUNT returns the number of unique nonnull values.
-
expression
-
Is an expression of any type except text, image, or ntext. 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(*) 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 getting rid of duplicates. It counts each row separately. This includes rows that contain null values.
int
COUNT(*) returns the number of items in a group. This includes 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.
For return values greater than 2^31-1, COUNT produces an error. Use COUNT_BIG instead.
A. Using COUNT and DISTINCT
The following example lists the number of different titles that an employee who works at Adventure Works Cycles can hold.
USE AdventureWorks;
GO
SELECT COUNT(DISTINCT Title)
FROM HumanResources.Employee;
GO
Here is the result set.
-----------
67
(1 row(s) affected)
B. Using COUNT(*)
The following example finds the total number of employees who work at Adventure Works Cycles.
USE AdventureWorks;
GO
SELECT COUNT(*)
FROM HumanResources.Employee;
GO
Here is the result set.
-----------
290
(1 row(s) affected)
C. Using COUNT(*) with other aggregates
The following example shows that COUNT(*) can be combined with other aggregate functions in the select list.
USE AdventureWorks;
GO
SELECT COUNT(*), AVG(Bonus)
FROM Sales.SalesPerson
WHERE SalesQuota > 25000;
GO
Here is the result set.
----------- ---------------------
14 3472.1428
(1 row(s) affected)
Reference
Aggregate Functions (Transact-SQL)
COUNT_BIG (Transact-SQL)
OVER Clause (Transact-SQL)
Help and Information
Getting SQL Server 2008 Assistance