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
  • 191 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Midgard

Dani AI

Generated

A compact, more robust pattern that builds on 's AVG suggestion and 's aggregation idea. The key points: exclude zero ratings from the average, keep movies with no positive ratings visible (so they can appear at the bottom or be filtered later), and write the GROUP BY so it works with strict MySQL modes.

Example (keeps movies with no positive ratings, shows count of valid ratings, rounds the average):

SELECT m.RecNum, m.Title,
       ROUND(AVG(CASE WHEN r.Rating > 0 THEN r.Rating END), 2) AS avg_rating,
       COUNT(CASE WHEN r.Rating > 0 THEN 1 END) AS num_ratings
FROM Movies AS m
LEFT JOIN Reviews AS r ON m.RecNum = r.MovieNum
GROUP BY m.RecNum, m.Title
ORDER BY avg_rating DESC, num_ratings DESC;

Notes and practical tips:

  • The CASE expression lets AVG ignore 0s while a LEFT JOIN preserves movies that have no positive ratings (avg_rating will be NULL). Using an INNER JOIN with WHERE r.Rating > 0 would drop movies that only have zero/none.
  • To show only movies that do have at least one valid rating add a HAVING clause: HAVING COUNT(CASE WHEN r.Rating > 0 THEN 1 END) > 0.
  • To display a fallback (e.g., 0 or "No ratings") use COALESCE on the avg: COALESCE(ROUND(...), 0).
  • If ONLY_FULL_GROUP_BY is enabled, include non-aggregated columns in GROUP BY (or use ANY_VALUE for MySQL 5.7+).
  • Performance: index Reviews(MovieNum). If reads are frequent and Reviews are large, maintain a pre-aggregated table or update an avg/count column on Movies via triggers or a periodic job.
  • Schema suggestion: prefer NULL for "not rated" rather than 0, or use a separate flag — it makes filtering more natural and semantically clearer.

This keeps the thread solution concise while handling real-world edge cases and MySQL strict-mode behavior. Thanks to and for the starting ideas and to for confirming the original approach worked.

Recommended Answers

All 3 Replies

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.