CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
OhSnap
OhSnap

Reputation: 15

How can I generate SQL query with average score for movie from different tables?

I would very much appreciate your help with this query. My database (Oracle) has a table called MOVIES with fields like id and title. Then there is another one, SCORES which has fields like movie_id and score. It is a one-to-many relation.

Now, I'm looking for a way to get a SELECT that will give me a result like one below:

movie-title, avg(score where movie.id=scores.movie_id)

It looks simple at first glance, but I cannot pass through it. Any suggestions?

Upvotes: 0

Views: 202

Answers (2)

shim-kun
shim-kun

Reputation: 123

First off, you need to join the tables on the pk/fk

Then, in order to get the avg for each movie, you need to use group and specify the column (score) that you want.

In this case we are grouping by the id because it's unique and the movie title since it is in the select clause

select m.title, avg(s.score)
from movie m
inner join scores s
on m.id = s.movie_id
group by m.id, m.title

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You just need join and group by as follows:

Select m.title, avg(s.score)
From movies m join scores s
On m.id = s.movie_id
Group by m.id, m.title

Upvotes: 1

Related Questions