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

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)

See Also

Reference

Aggregate Functions (Transact-SQL)

COUNT_BIG (Transact-SQL)

OVER Clause (Transact-SQL)