1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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