I have two tables, as shown below. In the first table you have ids for movie bundles and the ids for the movies that are inside that bundle. 1 = Disney movie bundle, 2 = Warner Brothers movie bundle, 3 = Marvel movie bundle, 4 = X movie bundle.
In the second table you have ids for the movies and their average rating.
I was wondering how to get the rating of the bundle using the ratings of the movie.
| Movie Bundle | movie id |
|---|---|
| 1 | 1 |
| 1 | 5 |
| 1 | 7 |
| 2 | 1 |
| 2 | 2 |
| 2 | 4 |
| 2 | 6 |
| 2 | 8 |
| 3 | 10 |
| 3 | 11 |
| 4 | 11 |
| movie id | rating |
|---|---|
| 1 | 4.0000 |
| 2 | 5.0000 |
| 3 | 2.0000 |
| 4 | 3.5000 |
| 5 | 1.0000 |
| 6 | 3.0000 |
| 7 | 4.0000 |
| 8 | 5.0000 |
| 9 | 3.0000 |
| 10 | 2.0000 |
| 11 | 4.5000 |
| 13 | 5.0000 |
So the resulting table would look like:
| movie bundle | rating |
|---|---|
| 1 | 3.0000 |
| 2 | 4.1000 |
| 3 | 3.2500 |
| 4 | 4.5000 |
CodePudding user response：
GROUP BY with aggregation AVG
SELECT a.bundle_id, AVG(b.rating) rating
FROM bundles a
JOIN ratings b ON a.movie_id = b.movie_id
GROUP BY a.bundle_id