Count All vs Count Distinct in SQL Server

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

Count(All) and Count(Distinct) are two aggregate functions in SQL Server. 
 
Count function is a part of the SQL Server's 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.
 
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() Function in SQL Server

 
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 satisfying the where condition. NULL values will not be counted by the Count function.
 
Example
  1. SELECT  [EmpID]  
  2.       ,[EmpName]  
  3.       ,[EmpSalary]  
  4.   FROM [master].[dbo].[Employee]  
  5.   Go  
  6.   select Count(empname) as CountResult from [Employee]  
Output
 
Count-function-in SQL-Server.jpg 
 

Count(All ColumnName) Function

 
The COUNT(All ColumnName) function is similar to Count(ColumnName). It also produces the same result as Count(ColumnName).
 
Example
  1. SELECT  [EmpID]  
  2.       ,[EmpName]  
  3.       ,[EmpSalary]  
  4.   FROM [master].[dbo].[Employee]  
  5.   Go  
  6.   select Count(All empname) as CountResult from [Employee]  
Output
 
Count-all-function-in SQL-Server.jpg 
 

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
  1. SELECT  [EmpID]  
  2.       ,[EmpName]  
  3.       ,[EmpSalary]  
  4.   FROM [master].[dbo].[Employee]  
  5.   Go  
  6.   select Count(Distinct empname) as CountResult from [Employee]  
Output
 
Count-distinct-function-in SQL-Server.jpg