How to Count Distinct Values in MySQL?

The COUNT DISTINCT function is used to count the number of unique rows in a specified column(s). In simple words, this method is used to count distinct or unique values in a particular column(s).

In this article, we are going to learn how we can use the Count Distinct function in different types of scenarios. We will explore its wide use case with clear and concise examples and their respective explanations.

MySQL Count Distinct Values in a Column

The aggregate function COUNT() along with the DISTINCT keyword gives distinct values in a column. Using them, we can easily get the occurrence of unique values for our specified column.

Getting the count of distinct or unique values for a particular column is very useful in data analysis, as duplicate data can ruin the findings. The COUNT( DISTINCT column name(s)) method is used in gathering statistical insights.

Note: The COUNT DISTINCT function does not count NULL values. It only counts NON-NULL unique values.

Count Distinct Syntax

SELECT COUNT(DISTINCT column_name) 
FROM table_name;

Count Distinct Values in MySQL Example

We can use the below methods to count distinct values in MySQL.

1. Count Distinct Values Without Alias
2. Count Distinct Values With Alias
3. Count Distinct Values from Multiple Columns in MySQL

To perform operations related to counting distinct values in MySQL, we need to create a table in our data base first. Lets create a table.

MySQL

CREATE TABLE geeksforgeeks(
  id int PRIMARY KEY,
  name varchar(100),
  questions int,
  total_score int,
  potd_streak int
 );
INSERT INTO geeksforgeeks(id,name,questions,total_score,potd_streak)
  VALUES(1001,'Vishu',150,500,200),
  VALUES(1002,'Neeraj',140,200,210),
  VALUES(1003,'Aayush',150,500,100),
  VALUES(1004,'Sumit',140,200,200),
  VALUES(1005,'Harsh',150,400,100);

Output:

Table – geeksforgeeks

As we can observe, our table is successfully created in our data base with our given values. Now we are good to go.

1. Count Distinct Values Without Alias Example

In this example, we are going to count number of records which have distinct values for ‘questions’ columns. But for this example we are not going to provide any column name for the result. Lets see the query.

Query:

SELECT COUNT (DISTINCT questions)
FROM geeksforgeeks;

Output:

Distinct-questions

In the above Output, we can clearly observe that there are only two values which are distinct i.e. 150, 140. In short, this query is counting the number of different values in questions column and in this case its two i.e. 150, 140. There is a thing to notice that the output shown has no heading in it as we have specified none.

In next example we are going to see how we can provide a distinct name for our result block.

2. Count Distinct Values with Alias Example

In this example, we are going to count number of distinct or unique values in ‘total_score’ column. But this time we are going to specify a heading to our result block using alias. Lets see the implementation.

Query:

SELECT COUNT (DISTINCT total_score) AS distinct_score
FROM geeksforgeeks;

Output:

From the above shown image, we can clearly notice that 3 has been displayed. Moving to our main table, we can see that there are only three distinct or unique values for ‘total_score’ i.e. 200, 400, 500. The main difference between the previous output from example 1 and this output is that in this output, a heading has been displayed which is specified by us.

3. Count Distinct Values from Multiple Columns Example

In this example, we are going to count distinct or unique values from more multiple columns. We are using two columns i.e. questions, total_score for counting distinct values. If both the columns have same values at a particular column then that value will not be counted. Lets see the implementation.

Query:

SELECT COUNT (DISTINCT CONCAT(questions,total_score)) AS unique_records
FROM geeksforgeeks;

Output:

Distinct- Multiple records

From the above output, we can clearly see that three is displayed as result. Moving to the main table, we can spot that there are only three records which have unique values :-

1. (150, 500)
2. (140, 200)
3. (150, 400)

We have also specified a heading of the output with the help of alias same as we have done in the previous output.

Conclusion

Counting distinct values plays a crucial role in various fields like data analysis. We can simple count distinct values using COUNT() method with DISTINCT keyword along with column name(s).

We have explained various use cases of counting distinct values in My SQL with clear and concise examples along with their respective explanations.