I am trying to get this select statement to execute but i keep getting error message 8120, everything looks right and my objective is to Provide a list of all advisors and the number of active students assigned to each. Filter out any advisors with more than 1 student. can any one help?
SELECT Advisors.FirstName, Advisors.LastName, Advisors.AdvisorID , COUNT(StudentID) AS 'COUNT(Students)'
On Advisors.AdvisorID = Students.AdvisorID
Group BY Advisors.AdvisorID
Having COUNT(StudentID) = '1'