Search code examples
mysqlsqlratingaverage

List with artistnames sorted by average rating from MySQL tables

I have this table "Ratings" containing an USER_ID, Artist_ID and a Rating (int, the score assigned by a user). And of course a table "Artists" with among others a field "ID" and "Name".

I am trying to get a descending list with artistnames, sorted by the AVG score per artists. First I thought this wouldn't be this hard, but I keep on fiddling...

Here's where I'm stuck. I have to figure out how it can calculate the AVG() of the id's that occur multiple times. And I've tried a variety of queries using the DISTINCT keyword but gained no success.

For example:

SELECT Name, Rating
FROM Artists as a
INNER JOIN Ratings as r
ON a.ID = r.Artists_ID 
ORDER BY r.Rating DESC;

Gives me the result:

Name                Rating
"The Mars Volta"    9.5
"Simon Felice"      9.0
"Laura Gibson"      8.0
"Calexico"          7.0
"Mira"              7.0
"Guido Belcanto"    6.0
"Guido Belcanto"    1.0

As you can see, the artist "Guido Belcanto" has more than 1 rating. I have no clue at the moment on how to calculate the AVG() of those ID's that occur more than once. It's probably basic MySQL but I'm maybe searching in the wrong direction

Solution

  • You forgot a GROUP BY on your query and actually calculating the average of ratings you pull in (with the AVG function):

    SELECT Name, AVG(Rating) AS AVGRating
FROM Artists as a
INNER JOIN Ratings as r
    ON a.ID = r.Artists_ID
GROUP BY Name
ORDER BY AVGRating DESC;

    Though it is probably better to do the group by on the key of the artist's table (with enough data it is not unthinkable to have several artists with the same name).