Search code examples
sqlsuminner-joinaverage

SQL get sum of multiple averages

I have a table with Songs, users and users rating of the song.

ratings.*

userID songID rating
95 1 8
12 1 6
95 1 8
13 1 6
81 2 3
42 2 1
51 3 6
22 3 6

I also have a table thet matches songID's and artists.

songs.*

songID artist
1 Michal Jackson
2 Queen

What I need is to get the average rating of each song, and then take the sum to find a total rating for the artist.

So here, ill try to show with the input, Michel Jackson.

SELECT SUM( AVG(ratings.rating WHERE songID=1) + AVG(ratings.rating WHERE songID=3) )

Wanted output

Input, artist Output, total rating
"Michal Jackson" 13
"Queen" 2

Every answer is really appreciated, thank you.

Solution

  • You could use a derived table (using a subquery as a table):

    SELECT
    songID
    , SUM(average_rating) AS total_rating
FROM (
        SELECT
            songID
            , artist
            , AVG(rating) AS average_rating
        FROM songs
        GROUP BY songID
    ) AS song_ratings
GROUP BY artist