SQL Order by Results Count

Question

I have a table with data like the following:

Meal ID   |   Child    |  Fruit

1              Child A     Apple
2              Child B     Orange
3              Child A     Banana
4              Child A     Orange
5              Child C     Banana
6              Child A     Banana
7              Child C     Banana
8              Child A     Banana

and I need to order the results, highest to lowest, based on the fruit eaten, e.g. if the search query is Banana, the results will display as follows:

Banana Eaters (Highest to Lowest)

Child   |   Number of Bananas eaten

Child A     3
Child C     1

I'd start with:

 $sql = "SELECT * FROM meals WHERE Fruit = 'Banana'";

But how do I sort the unique individual Banana-eaters as their own entry, then order the results by the ones who ate the most?

Answer 1

select Child, count(*) as 'Number of bananas eaten' from meals
where Fruit = 'Banana'
group by Child
order by count(*) desc

Answer 2

If you don't want to see "Child B", then filter before aggregation:

select Child, count(*) as num_bananas
from meals
where Fruit = 'Banana'
group by Child
order by count(*) desc;

Answer 3

Group by child and use conditional aggregation:

select child, sum(fruit = 'Banana') `Number of Bananas eaten`
from meals
group by child
order by `Number of Bananas eaten` desc 

If you want to exclude children who did not have any bananas:

select child, sum(fruit = 'Banana') `Number of Bananas eaten`
from meals
group by child
having `Number of Bananas eaten` > 0
order by `Number of Bananas eaten` desc 

See the demo.

Answer 4

use case when

select child,sum(case when Fruit='Banana' then 1 else 0 end) as bananaeaten
from meals
group by child
order by bananaeaten desc