title: How to count occurrences in SQL without creating duplicates?
body: <p>I have a table of many movies all with associated with different Director IDs, I need to count the number of movies made by the same Director ID and what that Director's Name is.</p>
  
  <p>My restrictions are to show only the Directors that have made 5 or more movies. I have 2 separate tables, One with the Movie names and Director IDs and one with the Director IDs and Director Names.</p>
  
  <p>I have come up with the following query based on looking through other peoples answers on this website:</p>
  
  <pre>Select A.Director_name, C.cnt
  from M_Movie B 
      Inner Join (select Director_ID, Director_Name
      from M_Director) A on B.Director_ID = A.Director_ID
          Inner Join (select Director_ID, count(Director_id) as cnt
          from M_Movie
          group by Director_ID) C on B.Director_ID = C.Director_ID
  where cnt >= 5
  order by cnt desc;</pre>
  
  <p>The above code delivers me the desired answer by listing the Director's Name and the number of movies they directed. - but <strong>Heres the Problem...</strong> </p>
  
  <pre>Director Name:       Number of Movies(CNT):<br>
  "Director A"           10
  "Director A"           10
  "Director A"           10
  "Director A"           10... (Happens 10 Times)
  "Director B"           8
  "Director B"           8
  "Director B"           8.... (Happens 8 Times)
  "Director C"           7</pre>
  
  <p>The answer lists "Director A" (who directed 10 movies) 10 times with the "CNT" as 10, and then next in the list is "Director B" (who directed 8 movies) 8 times with the "CNT" as 8, and then "Director C" is listed 7 times and so on...</p>
  
  <p>I would ideally like to have:
  <Pre>
  Director Name:       Number of Movies(CNT):<br>
  "Director A"           10
  "Director B"           8
  "Director C"           7</pre>
  With no repeats, just the listed names of the Directors and the Number of Movies they Directed.</p>
  
  <p>PLEASE HELP!</p>