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.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
Arguments
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.
Return Types
int
Remarks
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.
Examples
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 AdventureWorks2012;
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 AdventureWorks2012;
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 AdventureWorks2012;
GO
SELECT COUNT(*), AVG(Bonus)
FROM Sales.SalesPerson
WHERE SalesQuota > 25000;
GO
Here is the result set.
----------- ---------------------
14 3472.1428
(1 row(s) affected)
C. Using the OVER clause
The following example uses the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values for each department in the HumanResources.Department table.
USE AdventureWorks2012;
GO
SELECT DISTINCT Name
, MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
, MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
, AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;
Here is the result set.
Name MinSalary MaxSalary AvgSalary EmployeesPerDept
----------------------------- --------------------- --------------------- --------------------- ----------------
Document Control 10.25 17.7885 14.3884 5
Engineering 32.6923 63.4615 40.1442 6
Executive 39.06 125.50 68.3034 4
Facilities and Maintenance 9.25 24.0385 13.0316 7
Finance 13.4615 43.2692 23.935 10
Human Resources 13.9423 27.1394 18.0248 6
Information Services 27.4038 50.4808 34.1586 10
Marketing 13.4615 37.50 18.4318 11
Production 6.50 84.1346 13.5537 195
Production Control 8.62 24.5192 16.7746 8
Purchasing 9.86 30.00 18.0202 14
Quality Assurance 10.5769 28.8462 15.4647 6
Research and Development 40.8654 50.4808 43.6731 4
Sales 23.0769 72.1154 29.9719 18
Shipping and Receiving 9.00 19.2308 10.8718 6
Tool Design 8.62 29.8462 23.5054 6
(16 row(s) affected)