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
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
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
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
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