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.

  Important

  Distinct aggregates, for example AVG(DISTINCT column_name), COUNT(DISTINCT column_name), MAX(DISTINCT column_name), MIN(DISTINCT column_name), and SUM(DISTINCT column_name), are not supported when you use CUBE or ROLLUP. If they are used, the Microsoft SQL Server 2005 Database Engine returns an error message and cancels the query.

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

See Also

Reference

Aggregate Functions (Transact-SQL)
COUNT_BIG (Transact-SQL)
OVER Clause (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance