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)
The docs above state "May be followed by the OVER clause", but that is not true in all cases. In particular, you cannot use the OVER clause with COUNT(DISTINCT expression). For example, you cannot do this:
SELECT COUNT(DISTINCT colX) OVER(PARTITION BY colY)
FROM tableT
Personally, I love the OVER clause for RS reports, and I'm disappointed that it cannot be used in this case. If you feel the same way, you can tell Microsoft you want this feature by voting for it on Connect: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254393
