ordering by total number of rows

Here is my scenario,

"movies" table on the mysql database,

director_id     movie
-----------     ------
1                movie1
1                movie2
1                movie3
3                movie4
3                movie5
2                movie6
2                movie7
2                movie8
2                movie9

how can i order directors by number of the movies they 开发者_JAVA百科have as descending ?

Like this

2 -> 4 movies

1 -> 3 movies

3 -> 2 mvoies

SELECT director_id, COUNT(*) as TotalMovies FROM movies
  GROUP BY director_id
  ORDER BY COUNT(*) DESC

Make a calculated column based on the count of the records for each Director, and order on that.

ie SELECT Directors, count(DISTINCT directors) FROM movies GROUP BY directors;