Sort based on different table field avg

[Midgard]

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

[anand01]

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`

[smantscheff]

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

[Midgard]

Yeah, that did it...Many many thnx!

-Midgard