How Can I Efficiently Find the Most Frequently Consumed Food Item per Country in SQL?

Efficient Query for Finding Most Common Values in SQL

Consider a table with the following structure:

• country: Textual representation of a country
• food_id: Unique identifier for each type of food
• eaten: Date when the food was consumed

The task at hand is to identify the food item consumed most frequently for each country. A common approach involves creating multiple temporary tables to calculate counts and maximum values, as shown in the question. However, this method can be cumbersome and may necessitate breaking ties when multiple foods have the same count.

A Simplified Solution

PostgreSQL 9.4 introduced the mode() function, which offers a more straightforward approach:

select mode() within group (order by food_id)
from munch
group by country

This query returns the most common food item for each country, effectively eliminating the need for complex calculations and temporary tables. The results are presented as follows:

country | mode
--------------
GB      | 3
US      | 1

The above is the detailed content of How Can I Efficiently Find the Most Frequently Consumed Food Item per Country in SQL?. For more information, please follow other related articles on the PHP Chinese website!