MySQL COUNT DISTINCT

Summary: in this tutorial, you will learn how to use the MySQL COUNT DISTINCT function to count the number of unique values in a specific column of a table.

Introduction to MySQL COUNT DISTINCT function

The COUNT DISTINCT allows you to count the number of unique values in a specific column of a table.

Here’s the basic syntax for using the COUNT DISTINCT function:

SELECT COUNT(DISTINCT column_name)
FROM table_name;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• COUNT DISTINCT: The function for counting unique values.
• column_name: The name of the column for which you want to count distinct values.
• table_name: The name of the table that contains the column_name.

In practice, you use the COUNT DISTINCT when you want to find out how many unique values a present in a column.

MySQL COUNT DISTINCT examples

Let’s take some examples of using the COUNT DISTINCT function. We’ll use the employees table from the sample database for the demonstration:

The following query retrieves first name, last name, and job title from the employees table:

SELECT firstName, lastName, jobTitle 
FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+-----------+----------------------+
| firstName | lastName  | jobTitle             |
+-----------+-----------+----------------------+
| Diane     | Murphy    | President            |
| Mary      | Patterson | VP Sales             |
| Jeff      | Firrelli  | VP Marketing         |
| William   | Patterson | Sales Manager (APAC) |
| Gerard    | Bondur    | Sale Manager (EMEA)  |
| Anthony   | Bow       | Sales Manager (NA)   |
| Leslie    | Jennings  | Sales Rep            |
| Leslie    | Thompson  | Sales Rep            |
| Julie     | Firrelli  | Sales Rep            |
| Steve     | Patterson | Sales Rep            |
| Foon Yue  | Tseng     | Sales Rep            |
| George    | Vanauf    | Sales Rep            |
| Loui      | Bondur    | Sales Rep            |
| Gerard    | Hernandez | Sales Rep            |
| Pamela    | Castillo  | Sales Rep            |
| Larry     | Bott      | Sales Rep            |
| Barry     | Jones     | Sales Rep            |
| Andy      | Fixter    | Sales Rep            |
| Peter     | Marsh     | Sales Rep            |
| Tom       | King      | Sales Rep            |
| Mami      | Nishi     | Sales Rep            |
| Yoshimi   | Kato      | Sales Rep            |
| Martin    | Gerard    | Sales Rep            |
+-----------+-----------+----------------------+
23 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that the employees table has 23 rows.

1) Using COUNT DISTINCT to get the number of unique job titles

The following example uses the COUNT DISTINCT function to get the unique number of job titles from the jobTitle column of the employees table:

SELECT
  COUNT(DISTINCT jobTitle) 
from 
  employees;Code language: SQL (Structured Query Language) (sql)

In this query:

• COUNT( DISTINCT jobTitle): Counts the number of distinct values in the jobTitle column.
• FROM employees: Specifies the table from which to retrieve the data.

When you execute the query, MySQL will return the following output:

+--------------------------+
| count(distinct jobTitle) |
+--------------------------+
|                        7 |
+--------------------------+Code language: SQL (Structured Query Language) (sql)

You can use a column alias to assign a more meaningful name to the output column as follows:

SELECT
  COUNT(DISTINCT jobTitle) uniqueJobTitleCount
from 
  employees;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| uniqueJobTitleCount |
+---------------------+
|                   7 |
+---------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using COUNT DISTINCT to get the number of unique first names

The following example uses the COUNT DISTINCT to get the number of unique first names of employees:

SELECT
  COUNT(DISTINCT firstName) uniqueFirstNameCount
from 
  employees;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------+
| uniqueFirstNameCount |
+----------------------+
|                   21 |
+----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

COUNT DISTINCT and NULL

In MySQL, the COUNT DISTINCT function does not count NULL values. It only counts unique, non-null values in the specified column. In other words, if a column has null values, the COUNT DISTINCT function will ignore NULL values from the count.

Let’s take a look at the following example.

First, create a new table contacts that has two columns id and name:

CREATE TABLE contacts(
   id INT AUTO_INCREMENT,
   name VARCHAR(50),
   PRIMARY KEY(id)
);

Second, insert four rows into the contacts table:

INSERT INTO contacts(name)
VALUES ("John"), ("Jane"),(NULL), ("Jane");Code language: PHP (php)

Third, select data from the contacts table:

SELECT * FROM contacts;

Output:

+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Jane |
|  3 | NULL |
|  4 | Jane |
+----+------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

Finally, count the number of unique names in the name column of the contacts table:

SELECT COUNT(DISTINCT name)
FROM contacts;

Output:

+----------------------+
| COUNT(DISTINCT name) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

In this example, the COUNT DISTINCT function ignores the NULL value and only counts unique non-null values.

Summary

• Use MySQL COUNT DISTINCT function to count the number of unique values in a column of a table.
• The COUNT DISTINCT function ignores NULL values from the count.