1

I have tried to implement the ideas from this post to order my output by the occurrences of each pair -

MySQL: Count occurrences of distinct values

What I need is to be able to take into consideration two different columns, not just "name". So what would be the most efficient way to order the output of the following table -

id         name        food
-----      ------      ------
1          Mark        apple
2          Mike        pear
3          Paul        apple
4          Mike        pear
5          Mike        banana
6          John        apple
7          Mark        pear

The expected order should be:

name      food      count
-----     ------    -----
Mike      pear      2
Mike      banana    1
Mark      apple     1
Mark      pear      1
Paul      apple     1
John      apple     1
Community
  • 1
  • 1
user1145643
  • 896
  • 5
  • 15
  • 26

1 Answers1

3

You've got to group by both columns if you want to count the same combinations in those columns:

SELECT name, food, COUNT(*) count
FROM yourtable
GROUP BY name, food
ORDER BY COUNT(*) DESC
VMai
  • 10,156
  • 9
  • 25
  • 34
  • That's very simple. Thank you. Would there be a way to limit the results for each individual food? ie: if food = pear LIMIT 3, if food = apple LIMIT 5 ? – user1145643 Aug 14 '14 at 21:34
  • Consider asking a new question with this requirement. Please explain what you mean: Will you limit the result of five rows with apple and three rows with pears. – VMai Aug 14 '14 at 21:38