title: SQL Server 'invalid column name' error
body: <p>I have (from the imdb database) a <code>casts</code> table and a <code>movie</code> table. I'm trying to find the name and cast size of the movie with the largest cast (distinct actors).</p>
  
  <p>This is what I came up with, but I'm getting an </p>
  
  <blockquote>
    <p>"invalid column name" error on "totalcount"</p>
  </blockquote>
  
  <p>Why?</p>
  
  <p>Also, does this query look right?</p>
  
  <pre><code>select name, count(distinct pid) as totalcount 
  from casts join movie on mid=movie.id 
  where totalcount =
      (select max(CastCount.total) 
      from (select count(distinct pid) as total from casts group by mid)CastCount) 
  group by name;
  </code></pre>