how to show frequency of a column value where the value of other columns are a certain value?-CodePudding

I have a table with columns. For example:

T
id  food    place       days
i1  fruit   city        d1
i2  meat    town        d2
i3  bread   village     d3
i4  fruit   city        d1
i5  meat    city        d1
i6  meat    city        d1
i7  bread   village     d3
i8  fruit   village     d3
i9  bread   city        d1

In MySQL, how to find what was the most frequent food(s) in a certain place on a particular day and display which food, what place and day it was and frequency of the food?

I have tried

select max(foodCount),place,days,food
from
(select count(food) as foodCount,place,days
from T
group by days,place,food) as s
group by place,days,foodcount;

And various others which gave errors or didn't get the right display.

Also, I think I cannot use order by and then limit because that doesn't show equally frequent values and I do not know how many equally frequent values there will be in advance.

Edit: possible desired output

days    place       food    foodCount
d1      city        fruit   2
d1      city        meat    2
d1      town        NULL?   NULL?
d1      village     NULL?   NULL?
d2      city        NULL?   NULL?   
d2      town        meat    1
d2      village     NULL?   NULL?
d3      village     bread   2
d3      city        NULL?   NULL?
d3      town        NULL?   NULL?

or if ignoring days/place combos where where was no food

days    place       food    foodCount
d1      city        fruit   2
d1      city        meat    2
d2      town        meat    1
d3      village     bread   2

or just for a particular combo of days/place e.g. d1,city

days    place       food    foodCount
d1      city        fruit   2
d1      city        meat    2

CodePudding user response：

You can try

WITH counts AS (
   SELECT day, place, food, count(food) AS foodCount
     FROM T
    GROUP BY food, place, day
), 
max_counts AS (
   SELECT day, place, food, foodCount, max(foodCount) over (partition by day, place) as maxCount
     FROM counts
)
SELECT * FROM max_counts WHERE foodCount = maxCount;

The first common table expression counts just counts the food by day, place and food. The second CTE max_counts adds the maximum foodCount 'grouped by' day and place to each row in counts. From this you can select those rows where the foodCount equals the maximum.maximum.