0

Hi-

Probably pretty simple for experienced mysql-ers, but I'm at a loss...

Say I have two tables: Movies & Reviews.

Movies has a RecNum field, a Title field & other info

Reviews has a MovieNum field (identifying which Movie RecNum it's for), a Rating field (1-5 if rated, 0 if not), and other data. A Movie may have any number of Reviews.

What I want to do is to select all of the Movies and sort them (ASC or DESC) based on the average of all Reviews for that particular movie where the Rating is not 0.

I believe there's a pretty easy way of doing this, but I can't figure it out...JOIN? GROUP? Something else? I dunno...

Any assistance appreciated...

Thnx!

-Midgard

3
Contributors
3
Replies
4
Views
5 Years
Discussion Span
Last Post by Midgard
0

hi Midgard,

I thing your table structure is
------------
movie
------------
movienum
moviename
------------

------------
ratting
------------
movienum
rattingpoints
------------

query will be

SELECT movie.movienum, movie.`moviename` , sum( ratting.ratingpoints ) as rattings
FROM movie INNER JOIN ratting ON movie.`movienum` = ratting.`movienum` GROUP BY `movienum`

Edited by anand01: n/a

0

Replace "sum" with "avg":

SELECT movies.movienum, movies.moviename, avg(reviews.ratingpoints) as ratings
FROM movies INNER JOIN reviews 
ON movies.movienum = reviews.movienum 
WHERE reviews.ratingpoints > 0
GROUP BY movienum

Edited by smantscheff: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.