title: SQL: Usage of count() - Confusion and explanation
body: <p>I am working with the following database tables in SQL (the entries are just examples): </p>
  
  <p>One table to hold information about a <strong>film</strong>: </p>
  
  <pre><code>****************** film ******************
  filmid (int)    |   title (string)              | prodyear (int)
  -----------------------------------------------------------------
  2919898         | Devil May Cry                     | 2011
  7246970         | Three Men Seeking Monsters        | 2010
  </code></pre>
  
  <p>One table to hold information about <strong>film participation</strong>:</p>
  
  <pre><code>****************** filmparticipation ******************
  partid (int)    | personid (int) | filmid (int) | parttype (string)
  --------------------------------------------------------------------
  17              | 17             | 52               | costume designer
  85              | 70             | 69               | director`enter code here`
  </code></pre>
  
  <p>And one table to hold information about a <strong>person</strong>:</p>
  
  <pre><code>****************** person ******************
  personid (int)  | lastname (string) | firstname (string)    | gender (char)
  --------------------------------------------------------------------------------
  167076          | Jones             | Anthony               | M
  197             | Mitchell          | Julia                 | F
  </code></pre>
  
  <p>Now for the <strong>QUESTION</strong>:</p>
  
  <p>1) I have tried writing a query for **Finding the names and total number of movies a person has participated in. My query looks like the following: </p>
  
  <pre><code>select 
      p.lastname, p.firstname, count(distinct f.filmid) as numberOfMovies
  from
      film f, filmparticipation x, person p
  where
      x.filmid = f.filmid and
      x.personid = p.personid
  group by 
      p.lastname, p.firstname
  </code></pre>
  
  <p>I believe I am having problems with <strong>count()</strong>, as it returns 1 for every tuple. </p>
  
  <p>2) A similar query, <strong>Get the filmid, number of people involved and production year (prodyear) for all movies directed by Christopher Nolan</strong>. My query is as follows: </p>
  
  <pre><code>select
      f.title, f.filmid, count (distinct x.partid), prodyear
  from
      film f, filmparticipation x, person p, filmparticipationinfo inf
  where
      p.firstname='Christopher' and
      p.lastname='Nolan' and
      x.personid = p.personid and
      x.parttype = 'director' and
      x.filmid = f.filmid
  group by
      f.title, f.filmid, prodyear
  order by
      prodyear asc;
  </code></pre>
  
  <p>Again, in the result, <strong>count()</strong> is set as 1 for every movie, which is incorrect. </p>
  
  <p>If anyone could explain how to properly use <strong>count()</strong>, it would be highly appreciated. </p>