如何在同一语句中计数两次

Question

我有一个表，其中每个条目代表一个购买。

感兴趣的列是Items、Customer。

我想输出

<Items, total items sold, total distinct client that bought it>

我得到的最远的是

select item, customer, count(customer) as `Total Sold`
from my_table
group by item, customer
order by item

这将输出

item1, customer1, total_bought_by_customer1
item1, customer2, total_bought_by_customer2

我想要的是

item1, total_bought, total_distinct_costumer_who_bought_it

我该怎么做？没有联接。

Answer 1

您需要将客户从分组中删除(假设客户是一个打字错误(，

select item, 
count(*) as total_bought, 
count(distinct customer) as total_distinct_custumer_who_bought_it
from my_table
group by item
order by item