The reason a column is not valid in the select list is that it is not included in an aggregate function or GROUP BY clause

Question

I received an error -

Column 'Employee.EmpID' is not valid in the select list because it is Not included in aggregate functions or GROUP BY clauses.

---

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 matches the answer given by Bill Karwin.

Correction to above, answer suitable for 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 SQL queries -

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

I don't understand why this error occurs. All I want to do is join the tables and then group all employees at a specific location together.

I think I partially explained my problem. Tell me if you can -

To group all employees working at the same location, we must first mention the LocationID.

Then we can't/don't mention each employee ID next to it. Instead we mention the total number of employees at the location i.e. we should SUM() the employees working at that location. I'm not sure why we go the latter way. So this explains the "it is not contained in either aggregate function" part of the error.

What is the explanation for the incorrect GROUP BY clause part?

Answer 1

If you set disable ONLY_FULL_GROUP_BY server mode (by default), your queries will run in MYSQL. But in this case, you are using a different RDBMS. So to make your query work, add all non-aggregated columns to your GROUP BY clause, like

SELECT col1, col2, SUM(col3) totalSUM
FROM tableName
GROUP BY col1, col2

Non-aggregated column means that the column will not be passed to aggregate functions, such as SUM, MAX, COUNT, etc.

Answer 2

Suppose I have the following tableT:

a   b
--------
1   abc
1   def
1   ghi
2   jkl
2   mno
2   pqr

I execute the following query:

SELECT a, b
FROM T
GROUP BY a

The output should have two lines, one for a=1 and the second for a=2.

But what should the value of b be displayed in these two lines? There are three possibilities for each case, and nothing in the query makes it clear which value to choose for b in each group. The meaning is very vague.

This demonstrates the single value rule which prohibits getting undefined results when running a GROUP BY query and including any column criteria in the select list that is not part of the grouping and will not appear in in aggregate functions (SUM, MIN, MAX, etc.).

Fixing it might look like this:

SELECT a, MAX(b) AS x
FROM T
GROUP BY a

Now it's obvious that you want the following result:

a   x
--------
1   ghi
2   pqr