Something

SELECT movie.name, max(total)
FROM (SELECT mid, count(pid) total
      FROM cast
GROUP BY mid) total_cast 
WHERE movie.id = cast.mid