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 database first. Let’s create a table.

CREATE TABLE w3wiki(
  id int PRIMARY KEY,
  name varchar(100),
  questions int,
  total_score int,
  potd_streak int
);

INSERT INTO w3wiki(id, name, questions, total_score, potd_streak)
VALUES
  (1001, 'Vishu', 150, 500, 200),
  (1002, 'Neeraj', 140, 200, 210),
  (1003, 'Aayush', 150, 500, 100),
  (1004, 'Sumit', 140, 200, 200),
  (1005, 'Harsh', 150, 400, 100);

Output:

As we can observe, our table is successfully created in our database 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 a number of records that have distinct values for ‘questions’ columns. But for this example, we are not going to provide any column name for the result. Let’s see the query.

Query:

SELECT COUNT(DISTINCT questions)
FROM w3wiki;

Output:

Explanation: In the above Output, we can observe that there are only two values that are distinct i.e. 150, 140. In short, this query is counting the number of different values in the questions column, and in this case,, it’s 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 the 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 a number of distinct or unique values in the ‘total_score’ column. But this time we are going to specify a heading to our result block using an alias. Let’s see the implementation.

Query:

SELECT COUNT(DISTINCT total_score) AS distinct_score
FROM w3wiki;

Output:

Explanation: From the above-shown image, we can notice that 3 have 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 multiple columns. We are using two columns i.e. questions, total_score for counting distinct values. If both the columns have the same values at a particular column then that value will not be counted. Let’s see the implementation.

Query:

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

Output:

Explanation: From the above output, we can see that three is displayed as a 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 an alias same as we have done in the previous output.

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

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.