SQL Server COUNT DISTINCT

Summary: in this tutorial, you will learn how to use the SQL Server COUNT DISTINCT to get the total number of unique values in a column of a table.

Introduction to the SQL Server COUNT DISTINCT

In SQL Server, the COUNT() function is an aggregate function that returns the total number of rows that meet a certain condition.

Sometimes, you may want to count the number of distinct values in a column that meets a condition. To do that, you can use the DISTINCT option:

COUNT(DISTINCT column_name)Code language: SQL (Structured Query Language) (sql)

The COUNT DISTINCT will count duplicate values as one. It’s important to note that the COUNT(DISTINCT) function completely ignores NULL when counting.

In practice, you often use the COUNT DISTINCT to get the total number of unique values in a column that satisfies a certain condition.

SQL Server COUNT DISTINCT examples

Let’s explore some examples of using the SQL Server COUNT DISTINCT.

1) Basic SQL Server COUNT DISTINCT example

First, create a new table called numbers that has an id column:

CREATE TABLE numbers(
   id INT
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the numbers table:

INSERT INTO numbers(id) 
VALUES 
  (1), 
  (2), 
  (3), 
  (3), 
  (NULL), 
  (NULL);Code language: SQL (Structured Query Language) (sql)

The numbers table has duplicate values 3 and NULL.

Third, use the COUNT DISTINCT to return the total number of distinct values in the id column:

SELECT 
  COUNT(DISTINCT id) 
FROM 
  numbers;Code language: SQL (Structured Query Language) (sql)

Output:

id_count
-----------
3Code language: SQL (Structured Query Language) (sql)

In this example, the query returns 3 which includes the numbers 1, 2, and 3. It counts the duplicate number 3 as 1 and ignores the NULL.

In the upcoming examples, we’ll use the production.products and production.brands tables from the sample database:

2) Counting distinct values in a column

The following example uses the COUNT DISTINCT to get the total number of model years in the production.products table:

SELECT 
  COUNT(DISTINCT model_year) model_year_count 
FROM 
  production.products;Code language: SQL (Structured Query Language) (sql)

Output:

model_year_count
----------------
4Code language: SQL (Structured Query Language) (sql)

The output indicates that the production.products table has four unique model year values.

3) Counting distinct values with a condition

The following example uses the COUNT DISTINCT to get the total number of model years that are greater than 2017 from the production.products table:

SELECT 
  COUNT(DISTINCT model_year) model_year_count 
FROM 
  production.products 
WHERE 
  model_year > 2017;Code language: SQL (Structured Query Language) (sql)

Output:

model_year_count
----------------
2Code language: SQL (Structured Query Language) (sql)

In this example:

• First, include only products whose model years are greater than 2017 using the WHERE clause.
• Second, count the distinct model year of the filtered rows.

4) Using COUNT DISTINCT with the GROUP BY clause example

The following example uses the COUNT DISTINCT to obtain the total number of model years for each brand name:

SELECT
  brand_name,
  COUNT(DISTINCT model_year) distinct_model_year
FROM
  production.products p
  INNER JOIN production.brands b ON b.brand_id = p.brand_id
GROUP BY
  brand_name
ORDER BY
  brand_name;Code language: SQL (Structured Query Language) (sql)

Output:

brand_name   | distinct_model_year
-------------+------------------
Electra      | 3
Haro         | 1
Heller       | 2
Pure Cycles  | 1
Ritchey      | 1
Strider      | 1
Sun Bicycles | 1
Surly        | 3
Trek         | 4
(9 rows)Code language: SQL (Structured Query Language) (sql)

In this example:

• Join the production.products table with the production.brands table using the values in the brand_id column.
• Group the rows by brand names using the GROUP BY clause.
• Count the distinct model year for each brand name using the COUNT DISTINCT.
• Sort the groups by the brand names using ORDER BY clause.

Summary

• Use the COUNT DISTINCT to count unique values in a column of a table.