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

Question

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

User · Answer

All I want to do is join the tables and then group all the employees   in a particular location together     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    In that case  lose the GROUP BY statement   All you need is  ORDER BY loc LocationID

User · Answer

Suppose I have the following table T   a   b -------- 1   abc 1   def 1   ghi 2   jkl 2   mno 2   pqr   And I do the following query   SELECT a  b FROM T GROUP BY a   The output should have two rows  one row where a 1 and a second row where a 2   But what should the value of b show on each of these two rows  There are three possibilities in each case  and nothing in the query makes it clear which value to choose for b in each group  It s ambiguous   This demonstrates the single-value rule  which prohibits the undefined results you get when you run a GROUP BY query  and you include any columns in the select-list that are neither part of the grouping criteria  nor appear 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 clear that you want the following result   a   x -------- 1   ghi 2   pqr

User · Answer

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

User · Answer

Your query will work in MYSQL if you set to disable ONLY FULL GROUP BY server mode  and by default It is   But in this case  you are using different RDBMS  So to make your query work  add all non-aggregated columns to your GROUP BY clause  eg  SELECT col1  col2  SUM col3  totalSUM FROM tableName GROUP BY col1  col2   Non-Aggregated columns means the column is not pass into aggregated functions like SUM  MAX  COUNT  etc

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

Examples related to sql

Examples related to group-by

Examples related to aggregate-functions