Msg 8120, Level 16, State 1, Line 1 Column 'Advisors.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Transact-SQL
1

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)'
From Advisors
Join Students
On Advisors.AdvisorID = Students.AdvisorID
Group BY Advisors.AdvisorID
Having COUNT(StudentID) = '1'

2

basic rules on GROUP BY whatever column not aggregated should be in the GROUP BY clause

on your query you have the following column

  • Advisors.FirstName
  • Advisors.LastName
  • Advisors.AdvisorID

But in your GROUP BY clause you only have

  • Advisors.AdvisorID

So you need to include the other 2 column in the GROUP BY clause

Having COUNT(StudentID) = '1'

COUNT () will return an INTEGER. You should not specify a string to it. Remove the single quote around 1

3 
SELECT A.FirstName, A.LastName, A.AdvisorID , '1' AS 'COUNT(Students)', A_1_student_only.StudentID
FROM (
    SELECT AdvisorID, MIN(StudentID) AS StudentID
    From Students
    Group BY AdvisorID
    Having COUNT(StudentID) = '1'
) AS A_1_student_only
Inner Join Advisors A
On A.AdvisorID = A_1_student_only.AdvisorID
4

This may work well too

SELECT A.FirstName, A.LastName, A.AdvisorID , 1 AS 'COUNT(Students)', 
A_1_student_only.StudentID
FROM (
    SELECT AdvisorID
    From Students
    Group BY AdvisorID
    Having COUNT(distinct StudentID) = 1
) AS A_1_student_only
Inner Join Advisors A
On A.AdvisorID = A_1_student_only.AdvisorID