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