计算不同的价值

我有一个数据集询问客户有多少宠物，例如。有没有一种方法可以用一个查询来计算不同的值(1、2、3等) ？ 谢谢！

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

我想要的是一个列表，上面写着:

  • 两个有两个宠物
  • 2只有3只宠物
  • 1人有4只宠物
113580 次浏览
小开

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.

小开

Ok, I deleted my previous answer because finally it was not what willlangford was looking for, but I made my point that maybe we were all misunderstanding the question.

I also thought of the SELECT DISTINCT... thing at first, but it seemed too weird to me that someone needed to know how many people had a different number of pets than the rest... thats why I thought that maybe the question was not clear enough.

So, now that the real question meaning is clarified, making a subquery for this its quite an overhead, I would preferably use a GROUP BY clause.

Imagine you have the table customer_pets like this:

+-----------------------+
|  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

would return:

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

as you need.

小开

I think this link is pretty good.

Sample output from that 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)
小开

You can use this:

select count(customer) as count, pets
from table
group by pets
小开 
SELECT CUSTOMER, COUNT(*) as PETS
FROM table_name
GROUP BY CUSTOMER;

提交答案