CODEWITHSUNDEEP
mysqlsqlcountsql-order-byinner-join
CatHalsey
CatHalsey

Reputation: 25

SQL Order Results Count by specific Query

I have a table with the following data:

Snack 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 another table listing out the fruits available in each Child's hometown grocery:

Child ID   |   Child Name   |  Local Fruit 1  | Local Fruit 2 |  Local Fruit 3 

1              Child A         Apple            Mango            Orange
2              Child B         Orange           Banana           Cherry
3              Child C         Banana           Mango            Apple

I need to order the results, from highest to lowest, for the number of a specific fruit eaten for each child, but only if said fruit is one of their hometown fruits;

If the query is "Orange", I need the results to display as below:

Hometown Orange Eaters (Highest to Lowest)

Child   |   Number of Fruits eaten

Child A     1
Child B     1

if the query is "Mango":

Hometown Mango Eaters

Child   |   Number of Fruits eaten

Child A     0
Child C     0

For Bananas (Child A is not displayed, despite eating the most bananas, as this is not one of their hometown fruits):

Hometown Banana Eaters

Child   |   Number of Fruits eaten

Child C     1
Child B     0

The SQL command I am currently using is as follows:

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

However, this only displays results for those that have already eaten the fruit; e.g. for the banana example, only the below is displayed:

Hometown Banana Eaters

Child   |   Number of Fruits eaten

Child C     1

How can I adjust the current command so that all "hometown fruit" children are displayed, regardless of if they have eaten said fruit or not?

Upvotes: 0

Views: 113

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

I think this is a join and aggregation with filtering:

select s.child, sum(c.fruit = $fruit) as num_fruit
from snacks s join
     child c
     on c.child_id = s.child
where $fruit in (c.local1, c.local2, . . . )
group by s.child
order by num_fruit desc;

Upvotes: 1

GMB
GMB

Reputation: 222632

Here is one option:

select child,
    (select count(*) from meals m where m.child = l.child and m.fruit = ?)  cnt
from listing l
where ? in (local_fruit_1, local_fruit_2, local_fruit_3)
order by cnt

The logic is to filter the listing table for rows where the concerned fruit can be found. Then, we use a correlated subquery to bring the count of fruits that were eaten.

The question marks represent the fruit whose analysis you want to bring.

Upvotes: 1

Related Questions