count most occurences with the WHERE from other table

I have a movie DB to organize my collection but also I'm using it to learn more of mySQL. along the development I find bumps (plenty of them) and right now my problem is this:

Table ACTORS:

• id_actor
• name
• sex

Table MOVIEACTORES:

• id_movieactores
• id_movie
• id_actor

I want to count the TOP 5 (top10, top20 or whatever!) of actors with most movies and then the Top5 of actresses with most movies!

I have this:

  SELECT filmesactores.id_actor, 
         COUNT( * ) AS contagem
    FROM filmesactores
GROUP BY id_actor
ORDER BY contagem DESC 
   LIMIT 10 

But this code doesn't discriminates actors开发者_如何学JAVA from actresses. I feel the solution might be simple but with my knowledge is out of my reach right now. Anyone?

Grouping by sex, name would separate actors' counts by gender, but since you want to apply the limit to each gender group (i.e. top 5 actors and top 5 actresses), perform two queries and UNION their results together:

SELECT name, COUNT(*) AS moviecount
FROM actors
JOIN movieactores ON actors.id_actor = movieactores.id_actor
WHERE sex = 'Male'
GROUP BY name
ORDER BY COUNT(id_movie) DESC
LIMIT 5

UNION

SELECT name, COUNT(*)
FROM actors
JOIN movieactores ON actors.id_actor = movieactores.id_actor
WHERE sex = 'Female'
GROUP BY name
ORDER BY COUNT(id_movie) DESC
LIMIT 5