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

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) 

• 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.

int

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.

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)

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)

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)

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

Getting SQL Server 2005 Assistance