COUNT (Transact-SQL)
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
- 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.
Important: Distinct aggregates, for example AVG(DISTINCT column_name), COUNT(DISTINCT column_name), MAX(DISTINCT column_name), MIN(DISTINCT column_name), and SUM(DISTINCT column_name), are not supported when you use CUBE or ROLLUP. If they are used, the Microsoft SQL Server 2005 Database Engine returns an error message and cancels the query.
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)
