Count and group by operation with two different tables-CodePudding

Movieid	Title
1	Toy Story
2	Jumanji
2	Jumanji
1	Toy Story

Second Table

Movieid	Rating
1	5
2	4
1	4
2	4

I have two tables with several columns like this.

My problem statement is

--Show top-rated movies with movieid and movie name (number of rating for certain movie should be greater than 200, print it in decreasing order)

I was trying like this, but I am having error like

Column 'vtopmovie.movieid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

create view vtopmovie as
select m.movieid, m.title, r.rating from movies m, ratings r
where m.movieid=r.movieid

select * from vtopmovie

select top(200) movieid, sum(rating) from vtopmovie
group by title

So, what could be the best possible query for this?

CodePudding user response：

Your query should like this if you want to get movieid and movie name with its rating

select top(200) movieid, title, sum(rating) from vtopmovie
group by movieid, title