There's a problem on SQLzoo under More Join operations for MySQL.
The problem asked is
List the films released in the year 1978 ordered by the number of actors in the cast.
The tables given are as follows, ord has value of 0 or 1, 1 for star role.
movie | actor | casting
id id movieid
yr name actorid
casting.movieid,COUNT(casting.actorid) AS num_actors
casting.movieid ) f
movie ON f.movieid = movie.id
movie.yr = 1978
You can do the join first, then do the grouping:
SELECT movie.* FROM movie JOIN casting ON movie.id = casting.movieid WHERE movie.yr = 1978 GROUP BY movie.id ORDER BY COUNT(*) DESC