• Article

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.

Topic link iconTransact-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 AdventureWorks2008R2;
GO
SELECT COUNT(DISTINCT JobTitle)
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
SELECT COUNT(*), AVG(Bonus)
FROM Sales.SalesPerson
WHERE SalesQuota > 25000;
GO

Here is the result set.

----------- ---------------------

14 3472.1428

(1 row(s) affected)