1

I have 2 different tables. Table 1 - "Ratings"

 movie_ID   Rating
 12100      5
 12200      4
 12100      4
 12230      5
 12200      3
 12450      4

And table 2 - "Movies"

 movie_ID  Title
 12100      ABBA
 12200      BCCA
 12230      AVDG
 12450      DLPS

I am trying to get the top 2 of the list based on the averages where a movie_ID has at least two ratings and the average that would be higher than 3.

I am using the following command but I dont know how to add the factors so that SQL would look for data with at least 2 ratings and an average rating higher than 3

 select top 2 m.movie_ID, m.title, avg(r.rating) as avg_rating
 from movies m inner join
 Ratings r>2
 on m.movie_ID = r.movie_ID
 group by m.movie_ID, m.title
 order by avg(r.rating) desc;
Alytas
  • 49
  • 4

2 Answers2

0

This question is aimed at teaching you the difference between WHERE and HAVING. Since you did GROUP BY, and you wish to filter by the number of ratings and the average, you need to remove WHERE and put the condition into HAVING:

select top 2
    m.movie_ID
,   m.title
,   avg(r.rating) as avg_rating
from movies m
inner join Ratings r on m.movie_ID = r.movie_ID
group by m.movie_ID, m.title
-- Since you are using GROUP BY, the filter goes here:
having count(*) >= 2 AND avg(r.rating) > 3
order by avg(r.rating) desc;
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
0

Tested on MSSQL

select top(2) Movies.movie_ID, Movies.Title, avg(Rating.Rating) as [Avg_Rating]
from Movies
join Rating on
    Movies.movie_ID =   Rating.movie_id and
    Rating.Rating > 1
Group by Movies.movie_ID, Movies.Title
having avg(Rating.Rating) >2
order by avg(Rating.Rating) Desc
M Danish
  • 480
  • 2
  • 5