Member Avatar for 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

  • 3 Contributors
  • 3 Replies
  • 166 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Midgard
Member Avatar for 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`
Edited by anand01 because: n/a
Member Avatar for 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
Edited by smantscheff because: n/a
Member Avatar for Midgard

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

-Midgard

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.