Count the various values

Question

I have a dataset asking the client how many pets they have. Is there a single request method, can I count different values ​​(1,2,3, etc.)? Thank!

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

What I need is a list:

• 2 has 2 pets
• 2 has 3 pets
• 1 out of 4 pets

Answer 1

You can make a separate account as follows:

 SELECT COUNT(DISTINCT column_name) FROM table_name; 

EDIT:

Following the clarifications and updating the question, I see now that this is a completely different question than we initially thought. "DISTINCT" has special meaning in SQL. If I understand correctly, you want something like this:

• 2 customers had 1 pet.
• 3 customers had 2 pets
• 1 user had 3 pets

Now, you probably want to use a subquery:

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

Let me know if this is not quite what you are looking for.

Answer 2

Well, I deleted my previous answer because finally it was not what Williamford was looking for, but I said that maybe we all did not understand the question.

I also thought about SELECT DISTINCT... at first SELECT DISTINCT... but it seemed a little strange to me that someone had to know how many people had a different number of pets than the rest ... that's why I thought maybe the question is was not clear enough.

So, now that the meaning of the real question is being clarified by making a subquery for this rather overhead, I would rather use the GROUP BY .

Imagine you have a customer_pets table as follows:

 +-----------------------+ | customer | pets | +------------+----------+ | customer1 | 2 | | customer2 | 3 | | customer3 | 2 | | customer4 | 2 | | customer5 | 3 | | customer6 | 4 | +------------+----------+ 

then

 SELECT count(customer) AS num_customers, pets FROM customer_pets GROUP BY pets 

will return:

 +----------------------------+ | num_customers | pets | +-----------------+----------+ | 3 | 2 | | 2 | 3 | | 1 | 4 | +-----------------+----------+ 

as you need.

Answer 3

I think this link is pretty good.

Sample output from this link:

 mysql> SELECT cate_id,COUNT(DISTINCT(pub_lang)), ROUND(AVG(no_page),2) -> FROM book_mast -> GROUP BY cate_id; +---------+---------------------------+-----------------------+ | cate_id | COUNT(DISTINCT(pub_lang)) | ROUND(AVG(no_page),2) | +---------+---------------------------+-----------------------+ | CA001 | 2 | 264.33 | | CA002 | 1 | 433.33 | | CA003 | 2 | 256.67 | | CA004 | 3 | 246.67 | | CA005 | 3 | 245.75 | +---------+---------------------------+-----------------------+ 5 rows in set (0.00 sec) 

Answer 4

You can use this:

 select count(customer) as count, pets from table group by pets