How can I count the number of columns a value has after a Where?

Javi Albors

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.

Gordon Linoff

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.

edited at
0

Comments

0 comments
Login to comment

Related

Count the number of columns that has a true value then divide it to the total number of columns

How can I count the number of comments and likes that a post has in MySQL?

How can I count the number of relationships each node has in Cypher?

How can I Count the number of columns in a .txt file in VBA

How can I count number of elements after query?

How can I count the number of lines in a file after a grep match?

How I can split HTML table after 'x' number of columns?

how I can count the number of positive value in each column?

How can I count the number of

How can I extract the number of occurences where a value occurs as the MAX?

How to count the number of columns with a single value?

How to get count of number of columns where the value is not zero row-wise in a pandas dataframe

count number of rows where value in two columns are both true

Count number of rows where a value appears in any of two columns in R

How to write a SQL to count total number of occurrences of value in column after group by while taking count as 1 if the group has the value?

R: how do I count the number of times a value has already appeared in a vector (or the number of times it appears to the left of that value)?

How can I count the number of unique pairs in a table that has a recursive relationship?

How can I count the number of times one item has been grouped together with another in R?

How can I produce a count on the number of times each word has occurred in the following

Rails: How can I count the total number of contacts a user has when "Contact" is polymorphic?

how can I split a dataframe by two columns and count number of rows based on group more efficient

How can I count the number of integer occurences from a txt file from certain columns in Java?

How can I count the number of occurrences of an element across two columns of a dataframe?

How can I count the number of zeros in all columns for a table in oracle sql

How can I write an excel formula to count the number of times values in two separate columns are the same on the same row

How can I count unique values by columns

How can I count columns in phalcon volt?

how I can count signs columns?

How can I group with multiple columns and count?