MySQL计数多个GroupBy

Question

我有这样的数据

id  otherid name    
1   123     banana
2   123     banana
3   123     banana
4   456     grape
5   456     grape
6   789     orange
7   111     banana

我怎么能得到这样的输出:(MySQL查询)

name    count
banana  2
grape   1
orange  1

Answer 1

试试这个:

SELECT 
f.`name`,
COUNT(DISTINCT (f.`otherid`)) 
FROM
`fruits` f 
GROUP BY f.`name` 

Answer 2

您可以使用COUNT与GROUP BY:

SELECT
`name`, COUNT(*)
FROM
write_you_table_name
GROUP BY
`name`,`otherid`

Answer 3

建议在count功能中使用distinct。但是，如果您不喜欢使用它，可以试试:

select name,count(*) from 
(select name from fruit group by name,otherid) t
group by name;

Answer 4

SELECT name, count(*) as count FROMtb_stockGROUP BY other_id