FB

How to Count Distinct Values in SQL

In this article, you’ll learn how to count the number of unique values in a column.

Sample Data

Let’s say we’ve got a table called “product” that looks like this:

id product_name price category
1 Chair 80 Dining
2 Desk 250 Office
3 Couch 600 Living
4 Office Chair 120 Office
5 Coffee Table 180 Living
6 Small Bin 40 Bathroom

We want to find the number of unique category values.

How can we do that?

 

Solution

To find the number of distinct values, we use a combination of the COUNT function and the DISTINCT keyword.

SELECT COUNT(DISTINCT category) AS count_category
FROM product;

The result of this query is:

count_category
4

It shows a value of 4 because there are 4 different category values:

  • Dining
  • Office
  • Living
  • Bathroom

Some values are repeated, but the repeated values are ignored in the COUNT function because we have used the DISTINCT keyword.

 

DISTINCT then COUNT

In the solution, the DISTINCT keyword goes inside the COUNT function.

What if you use the DISTINCT keyword outside of the COUNT function?

SELECT DISTINCT COUNT(category) AS count_category
FROM product;

The result would be:

count_category
6

Why does it show 6?

The COUNT function is written as COUNT(category), so it counts the number of values in the category column, which is 6.

Then, the DISTINCT keyword eliminates duplicates that are returned by the function. But, the function only returns one row: a number of 6. There are no duplicates of this.

Putting the DISTINCT outside the COUNT will run the COUNT function first and then find unique values returned by COUNT, but there is only one value returned by COUNT. Using a DISTINCT COUNT is almost always unnecessary.

Putting the DISTINCT inside the COUNT will count the number of distinct values, which is what you want to do.

1 thought on “How to Count Distinct Values in SQL”

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.