Problem
I got an error message –
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 corresponds to Bill Karwin’s response.
ExactaBox’s solution fits the above correction –
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 –
In order to run the SQL query –
select *
from Employee as emp full join Location as loc
on emp.LocationID = loc.LocationID
group by (loc.LocationID)
I’m not sure why I’m getting this problem. All I want to do is combine the tables and then put together all of the employees in a specific place.
I believe I’ve come up with a partial answer to my own query. Please let me know if this is ok –
We must first give the LocationID in order to group all employees who work at the same location.
Then we won’t be able to/won’t be able to list each employee ID next to it. Instead, we should state the entire number of employees in that place, i.e., we should SUM() the employees who work there. I’m not sure why we do it the second way. So it explains the part of the mistake that says “it is not contained in either an aggregate function.”
What’s the deal with the GROUP BY clause section of the error?
Asked by david blaine
Solution #1
Let’s say I’ve got the following table T:
a b
--------
1 abc
1 def
1 ghi
2 jkl
2 mno
2 pqr
In addition, I run the following query:
SELECT a, b
FROM T
GROUP BY a
There should be two rows in the output, one with a=1 and the other with a=2.
ies in each case, and the query makes no mention of which value to use for b in each group. It’s a bit of a misnomer.
This displays the single-value rule, which prevents undefined results when you execute a GROUP BY query with any columns in the select-list that aren’t part of the grouping criterion or present in aggregate functions (SUM, MIN, MAX, etc.).
It could be fixed in the following way:
SELECT a, MAX(b) AS x
FROM T
GROUP BY a
It’s evident at this point that you desire the following outcome:
a x
--------
1 ghi
2 pqr
Answered by Bill Karwin
Solution #2
If you disable ONLY FULL GROUP BY server mode in MYSQL, your query will function (and by default It is). However, you are utilizing a different RDBMS in this scenario. To make your query work, include all non-aggregated columns in the GROUP BY clause, for example.
SELECT col1, col2, SUM(col3) totalSUM
FROM tableName
GROUP BY col1, col2
Non-aggregated columns are those that are not passed through aggregated functions such as SUM, MAX, COUNT, and so on.
Answered by John Woo
Solution #3
It sounds like what you want is for the output of the SQL statement to list every employee in the company, but first all the people in the Anaheim office, then the people in the Buffalo office, then the people in the Cleveland office (A, B, C, get it, obviously I don’t know what locations you have).
Remove the GROUP BY statement in such scenario. ORDER BY loc.LocationID is all you need.
Answered by ExactaBox
Solution #4
Basically, what this error is saying is that if you are going to use the GROUP BY clause, then your result is going to be a relation/table with a row for each group, so in your SELECT statement you can only “select” the column that you are grouping by and use aggregate functions on that column because the other columns will not appear in the resulting table.
Answered by Alex W
Post is based on https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e