I need to execute a query where I have to show the title of the film and the avg valoration from the top 5 most rated films THAT HAVE OVER 2000 VOTES.
To execute that Query I dispose from the following tables (values have been already imported):
DROP TABLE IF EXISTS Film CASCADE;
CREATE TABLE Film(
id_film INT,
title VARCHAR(255),
year INT,
PRIMARY KEY(id_film)
);
DROP TABLE IF EXISTS User CASCADE;
CREATE TABLE User (
id_user INT,
gender VARCHAR(255),
postal_code VARCHAR(255),
id_ocupation INT,
PRIMARY KEY(id_user),
FOREIGN KEY(id_ocupation) REFERENCES Ocupation(id_ocupation),
);
DROP TABLE IF EXISTS User_Film;
CREATE TABLE User_Film (
id_film INT,
id_user INT,
rating INT,
timestamp INT,
PRIMARY KEY(id_film, id_user),
FOREIGN KEY (id_film) REFERENCES Film(id_film),
FOREIGN KEY (id_user) REFERENCES User(id_user)
);
And I have tried executing the following query. The query works fine but, how can I filter the output by the number of valorations?
select title, avg(rating)
from User U,
Film P,
User_Film UP
where UP.id_user = U.id_user
AND UP.id_film = P.id_film
group by title
order by avg(rating) desc
limit 5;
Also I guessed (as there's no field in any table named number_of_valorations or something similar) that the number of valorations is the amount of users who rates the same film.
You want a having
clause:
select title, avg(rating)
from User U join
User_Film UP
on UP.id_user = U.id_user join
Film P
on UP.id_film = P.id_film
group by title
having count(*) >= 2000
order by avg(rating) desc
limit 5;
Learn to use proper, explicit, standard JOIN
syntax. Never use commas in the FROM
clause.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments