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.
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)
