In this tutorial, we will introduce different methods to count distinct values.
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
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.
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.
Note: In the code above, we use the alias
distinct_first_names with as the
AS keyword in MySQL.