Whats wrong with this SQL Query?

[thomasjonesmona]

Error message is Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT     Film.FilmTitle, Person.FirstName
FROM         Person INNER JOIN
                      Role ON Person.PersonID = Role.PersonID INNER JOIN
                      Film ON Role.FilmID = Film.FilmID
WHERE Film.FilmID IN 
(
SELECT FilmID 
FROM 
Award  
JOIN Role  ON Award.RoleID = Role.RoleID)
AND Film.FilmID = Role.FilmID
AND Role.PersonID = Person.PersonID 
AND Role.PersonID IN
(SELECT     Role.PersonID
FROM         Person INNER JOIN
                      Role ON Person.PersonID = Role.PersonID INNER JOIN
                      Award ON Role.RoleID = Award.RoleID
GROUP BY Role.PersonID
HAVING COUNT(*) =
(

   SELECT MAX(COUNT(*) )
         FROM     Award INNER JOIN
             Role ON Award.RoleID = Role.RoleID)
)

[ck9663]

This part is your problem

HAVING COUNT(*) =
(
  
   SELECT MAX(COUNT(*) )
         FROM     Award INNER JOIN
             Role ON Award.RoleID = Role.RoleID)
)

Happy Coding!!!

~~ CK