mysql - Count distinct values

Question

I have a data set asking a customer how many pets they have for example. Is there a way with one query I can count the distinct values (1,2,3, etc)? Thanks!

+----------+------+
| Customer | Pets |
+----------+------+
|       20 |    2 |
|       21 |    3 |
|       22 |    3 |
|       23 |    2 |
|       24 |    4 |
+----------+------+

What I want is a list saying:

• 2 had 2 Pets
• 2 had 3 Pets
• 1 had 4 Pets

Answer 1

You can do a distinct count as follows:

SELECT COUNT(DISTINCT column_name) FROM table_name;

EDIT:

Following your clarification and update to the question, I see now that it's quite a different question than we'd originally thought. "DISTINCT" has special meaning in SQL. If I understand correctly, you want something like this:

• 2 customers had 1 pets
• 3 customers had 2 pets
• 1 customers had 3 pets

Now you're probably going to want to use a subquery:

select COUNT(*) column_name FROM (SELECT DISTINCT column_name);

Let me know if this isn't quite what you're looking for.