Count(All) vs Count(Distinct) in SQL Server 2012

In this article, you will see the SQL aggregate function count in two forms, Count(All) and Count(Distinct).

In this article, you will see the SQL aggregate function Count in two forms, Count(All) and Count(Distinct) in SQL Server. The Count function is a part of the aggregate functions. In aggregates we consider various types of functions like count, max, avg, min, and sum. In this article you consider the Count function which is used to count the number of rows in a database table. So let's have a look at a practical example of how to get the difference between the Count(All) and Count(Distinct) in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Count(ColumnName) Function

The COUNT() function is used to count the number of rows when you use a where condition with the Count function in a select statement. It will return the number of rows from the table satistying the where condition. NULL values will not be counted by the Count function.

Example

SELECT  [EmpID]

      ,[EmpName]

      ,[EmpSalary]

  FROM [master].[dbo].[Employee]

  Go

  select Count(empname) as CountResult from [Employee]

Output

Count(All ColumnName) Function

The COUNT(All ColumnName) function is similar to Count(ColumnName). It also produces the same result as Count(ColumnName).

Example

SELECT  [EmpID]

      ,[EmpName]

      ,[EmpSalary]

  FROM [master].[dbo].[Employee]

  Go

  select Count(All empname) as CountResult from [Employee]

 

Output

Count(Distinct ColumnName) Function

When the DISTINCT keyword is used, all duplicate values are eliminated before the function count is applied, or if you want to count the number of unique rows in the table. This form of Count does not count rows with null values for the column.

Example

SELECT  [EmpID]

      ,[EmpName]

      ,[EmpSalary]

  FROM [master].[dbo].[Employee]

  Go

  select Count(Distinct empname) as CountResult from [Employee]

Output