Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]

by

I got an error –

Column ‘Employee.EmpID’ is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.

select loc.LocationID, emp.EmpID
from Employee as emp full join Location as loc 
on emp.LocationID = loc.LocationID
group by loc.LocationID

This situation fits into the answer given by Bill Karwin.

correction for above, fits into answer by ExactaBox – 

select loc.LocationID, count(emp.EmpID) -- not count(*), don't want to count nulls
from Employee as emp full join Location as loc 
on emp.LocationID = loc.LocationID
group by loc.LocationID

ORIGINAL QUESTION –

For the SQL query –

select *
from Employee as emp full join Location as loc 
on emp.LocationID = loc.LocationID
group by (loc.LocationID)

I don’t understand why I get this error. All I want to do is join the tables and then group all the employees in a particular location together.

I think I have a partial explanation for my own question. Tell me if its ok –

To group all employees that work in the same location we have to first mention the LocationID.

Then, we cannot/do not mention each employee ID next to it. Rather, we mention the total number of employees in that location, ie we should SUM() the employees working in that location. Why do we do it the latter way, i am not sure.
So, this explains the “it is not contained in either an aggregate function” part of the error.

What is the explanation for the GROUP BY clause part of the error ?

4 Answers
4

Leave a Comment