Calculating average rating

Discussion in 'MySQL' started by karl_murphy, Jun 28, 2014.

0
  1. #1
    Hi,

    I have two sql tables; one called recipes and the other called ratings as show below:

    recipes
    id (INT)
    name (VARCHAR)
    method (TEXT)
    ingredients (TEXT)

    ratings
    id (INT)
    recipe_id (INT)
    rating (INT)

    Each recipe can have multiple ratings within the ratings table. What I'm trying to do is use PHP to query the database to find the 4 recipes with the highest average rating.

    Any help with this would be greatly appreciated.

    Thanks.
     
    karl_murphy, Jun 28, 2014 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    "SELECT t1.*, t2.* AVG(t1.rating) AS average FROM ratings t1 LEFT JOIN recipes t2 ON t1.recipe_id = t2.id GROUP BY t1.recipe_id ORDER BY average LIMIT 4"

    I think the above will work, more or less, but you might have to redo some of the logic to get the 4 with highest ratings overall. (I'm not entirely sure that the GROUP BY will work properly in this setting).
     
    PoPSiCLe, Jul 1, 2014 IP