SQL Server COUNT() Function

In SQL Server, the COUNT() is an aggregate function that returns the number of records in the SELECT query.

COUNT( [ ALL | DISTINCT] expression)

Parameters

ALL: Applies the aggregate function to all the values in the group. All values are counted. This is the default value.

DISTINCT: Applies the aggregate function to only distinct not null values.

expression: An expression of any type except text, ntext, or image.

The * specifies that the COUNT() function should consider all rows to arrive at the total table rows count.

• COUNT(*) returns the number of rows in a table. This includes duplicates and null values.
• COUNT(*) does not take any other parameter and does not support DISTINCT.
• COUNT(*) does not need an expression as it does not use any information about any particular column.

Return Value

Returns an integer value.

Use the COUNT_BIG() function for return values bigger than 2^31-1.

Example 1:

In this simple example, the COUNT(*) returns the total number of rows in the Employee table.

SELECT COUNT(*) AS EmpCount FROM Employee;

Example 2:

The following example get the total distinct departmentId value and the count of distinct DepartmentId value in the Employee table.

SELECT COUNT(DISTINCT DepartmentId) AS UniqueCount, COUNT(DepartmentId) AS ActualCount FROM Employee;