Search code examples
mysqlsqlinsert-update

SQL Update Query - Get average of multiple ratings and then transfer that number onto a different table

in this problem I have 2 tables, one that has a list of game reviews with a rating 1-5, and another is a list of video games. I want to find the average rating of each game and then insert those numbers into their respective places on another table. I'm not sure where to approach this whether using JOIN would be more appropriate or any other technique. I understand that in the code below I'm providing the general average as oppose to the specific game rating.

UPDATE GamesList
SET GamesList.AvgRating = AVG(GameReviews.Rating)
FROM GameReviews, GamesList
WHERE GamesList.GameTitle = GameReviews.GameTitle

What I'm trying to achieve here would look like this

GameTitle Average Rating
GTA 4.3
Witcher 4.6

From a table like this

Game Title Rating
GTA 4
GTA 4
GTA 5
Witcher 4
Witcher 5
Witcher 5

Any advice would be much appreciated.

Solution

  • I suggest not persisting your average data in a formal table. Instead, just use a query/view to generate the averages:

    SELECT gl.GameTitle, COALESCE(AVG(gr.Rating), 0) AS AvgRating
FROM GamesList gl
LEFT JOIN GameReviews gr
    ON gr.GameTitle = gl.GameTitle
GROUP BY gl.GameTitle;