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)
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.
Returns an integer value.
COUNT_BIG() function for return values bigger than 2^31-1.
In this simple example, the
COUNT(*) returns the total number of rows in the
SELECT COUNT(*) AS EmpCount FROM Employee;
The following example get the total distinct
departmentId value and the count of distinct
DepartmentId value in the
SELECT COUNT(DISTINCT DepartmentId) AS UniqueCount, COUNT(DepartmentId) AS ActualCount FROM Employee;