query mysql count multiple groupby-CodePudding

i've data like this

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

how can i get output like this: (with mysql query)

name    count
banana  2
grape   1
orange  1

CodePudding user response：

Try this:

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

CodePudding user response：

You can use COUNT with GROUP BY:

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

CodePudding user response：

Using distinct in the count function is recommended. But if you prefer not to using it, try this:

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

CodePudding user response：

SELECT name , count(*) as count FROM tb_stock GROUP BY other_id