How to Count Distinct Values in MySQL

In this tutorial, we will introduce different methods to count distinct values.

The COUNT() method in MySQL gives the total number of rows in the table as the output. However, In this article, we are interested in understanding how to calculate or count the number of distinct occurrences of an expression. The syntax to perform this operation can be written as COUNT(DISTINCT expression). This command gives us the total number of distinct non-null values as the output of the particular expression.

Let us see this method in action.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table along with rows with student first name and last name in it. In order to view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above code would give the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Count Distinct Values in MySQL

The MySQL COUNT (DISTINCT expression) function, as stated above, gives us the count of rows with unique non-null values. To count the number of students with unique first names, we use the following code.

-- Count the number of students with different first names
SELECT COUNT(DISTINCT stu_firstName) as distinct_first_names FROM student_details ;

The code above counts the number of distinct first names from the student_details table. The output of the code above is as follows.

distinct_first_names
7

Thus, we can see that the unique names (Preet, Rich, Veron, Geo, Hash, Sachin, and David) have been counted to generate the final count as 7.