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-PHP Chinese Network Q&A

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?

reply all (2)

P粉1558329412023-08-24 13:38:21 2 floor

If you set disableONLY_FULL_GROUP_BYserver mode (by default), your queries will run inMYSQL. But in this case, you are using a different RDBMS. So to make your query work,add all non-aggregated columnsto yourGROUP BYclause, 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 asSUM,MAX,COUNT, etc.

Like+0

Add Reply

P粉0900872282023-08-24 10:30:14 1 floor

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 fora=1and the second fora=2.

But what should the value ofbbe 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 thesingle value rulewhich 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: