MySQL count multiple GroupBy

Question

I have 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

Show us the query you tried, which isn't working yet. Show us the google search results you relied on when trying to come up with a solution. — J_H, May 23 '22 at 05:05
Does this answer your question? [Count the occurrences of DISTINCT values](https://stackoverflow.com/questions/1346345/count-the-occurrences-of-distinct-values) — FanoFN, May 23 '22 at 05:57
I actually agree. The exact link that I post as duplicate doesn't address the issue that you're having but I'm more than sure that this has been asked many times before. — FanoFN, May 23 '22 at 07:09

score 1 · Accepted Answer · answered May 23 '22 at 05:46

1

Try this:

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

answered May 23 '22 at 05:46

Asgar

1,920
2
8
17

Faizul Islam · Answer 2 · 2022-05-23T06:32:29.250

0

You can use COUNT with GROUP BY:

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

edited May 23 '22 at 06:32

answered May 23 '22 at 06:08

Faizul Islam

77
6

Your query won't give him the expected result. – Asgar May 23 '22 at 06:10
@Asgar Why not work? I checked It is working properly – Faizul Islam May 23 '22 at 06:29
if you read the question properly, you'll see that the expected output is grouped by name along with distinct otherid. Your query will give the total counts group by name – Asgar May 23 '22 at 06:30
ok but when it group_by with `name` and `otherid` then result is coming with along name and along otherid. it's working both distinct and also multiple group_by. – Faizul Islam May 23 '22 at 06:39
still your edited query also won't work. Check all data and try again – Asgar May 23 '22 at 06:43

score 0 · Answer 3 · answered May 23 '22 at 08:50

0

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;

answered May 23 '22 at 08:50

blabla_bingo

1,825
1
2
5

score -1 · Answer 4 · answered May 23 '22 at 06:26

-1

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

answered May 23 '22 at 06:26

nobita

1
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 23 '22 at 07:02

MySQL count multiple GroupBy

4 Answers4