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.