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.