How to Count Distinct Values in a Table In SQL Server

This tutorial will show how to create a query that retrieves a count of unique values in a column of a table. To make this query work, we will use the COUNT function and the DISTINCT statement. The COUNT function returns the number of rows that matches a specified definition and the DISTINCT statement can be used to return only distinct values. This tutorial will show how to use the COUNT function and DISTINCT statement in separate queries and then combine both in a single query to retrieve a count of unique values in a column.

Setting Up

We need to create a table called Employee with columns of EmployeeId, LastName, Country, and DepartmentId. We can create the layout of the Employee table with the CREATE TABLE statement.

 

 

Next we need to insert values into the table. We can do this by using the INSERT INTO statement.

 

 

We will run a quick query to see the results of the table, we simply type “SELECT * FROM Employee” for all columns and values in the table to be outputted upon execution of the query.

The COUNT Function

First let’s use the COUNT function to get acquainted with it. For this example we will use the COUNT function to count the number of EmployeeIds in the table. By adding EmployeeId in parenthesis next to COUNT we are telling SQL Server to call the function on EmployeeId.

 

 

Execute the query and it will retrieve the number of EmployeeIds and output them in a column called NumberOfEmployees.

The SELECT DISTINCT Statement

The SELECT DISTINCT statement selects only different values in a table, meaning it will not list duplicates. In this example, we will retrieve and list the distinct countries in the Country column.

 

 

Execute the query and it will list the five distinct countries in the table, regardless if a country appears more than once.

Count Distinct Values in a Table

In this example we will combine both the COUNT function and SELECT DISTINCT statement to count the number of distinct countries in the table. We simply write a query with the COUNT function as we did earlier, but this time, in the FROM clause we will write ‘SELECT DISTINCT Country FROM Employee’ as this lets SQL Server know to count only the distinct values in the Country column.

 

 

Execute the query and the NumberOfCountries column will appear with the number of distinct values in it.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Leave a Reply

Your email address will not be published. Required fields are marked *