CODEWITHSUNDEEP
phpmysqlsql
Rich
Rich

Reputation: 1779

MySQL query: generate a report of the top votes for the each food type

I have a user table with id, username and food_id columns. The idea is the user stored their favorite food and we come up with a league table of foods and I want to generate a report of the top votes for the each food type. I am using MySQL and PHP.

For clarity, here is an example of the table:

 id  food_id   username
 1   1         Bob
 2   100       Jane
 3   200       Andy
 4   1         Maggy
 5   100       Rich
 6   100       Mick
 7   1         Kevin

How do I write the query to list the foods that have the most votes. I want to limit the result to x number, say top 100. In this case I want the result to be as follows:

food_id score 1 3 100 4

I hope the question is clear enough. The query is beyond me but I am sure it must be possible to do it using DISTINCT and COUNT in some way or other. Perhaps it's sub queries?

Upvotes: 0

Views: 67

Answers (3)

renegm
renegm

Reputation: 620

select food_id, count(*) score
   from myTable
group by food_id
order by score desc limit 100



SELECT F.food_name,
    COUNT(*) AS score
FROM myTable AS M 
    INNER JOIN food_table AS F
        ON F.food_id = M.food_id
GROUP BY F.food_name
ORDER BY score DESC limit 100

Upvotes: 2

Nico Huysamen
Nico Huysamen

Reputation: 10417

SELECT   f.`food_id`, COUNT(*) AS `count`
FROM     `fav_food_table` f
GROUP BY f.`food_id`
ORDER BY `count` DESC
LIMIT    100;

Upvotes: 1

konsolenfreddy
konsolenfreddy

Reputation: 9671

select count(*) as top100 from table group by food_id order by top100 desc limit 100

Upvotes: 1

Related Questions