Home  >  Q&A  >  body text

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

<p>I received an error - </p> <blockquote> <p>Column 'Employee.EmpID' is not valid in the select list because it is Not included in aggregate functions or GROUP BY clauses. </p> </blockquote> <hr /> <pre class="brush:php;toolbar:false;">select loc.LocationID, emp.EmpID from Employee as emp full join Location as loc on emp.LocationID = loc.LocationID group by loc.LocationID</pre> <p>This situation matches the answer given by Bill Karwin. </p> <p>Correction to above, answer suitable for ExactaBox - </p> <pre class="brush:php;toolbar:false;">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</pre> <hr /> <p><strong>Original question -</strong></p> <p>For SQL queries -</p> <pre class="brush:php;toolbar:false;">select * from Employee as emp full join Location as loc on emp.LocationID = loc.LocationID group by (loc.LocationID)</pre> <p>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. </p> <p><strong>I think I partially explained my problem. Tell me if you can -</strong> </p> <p>To group all employees working at the same location, we must first mention the LocationID. </p> <p>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. </p> <p>What is the explanation for the incorrect <strong><code>GROUP BY</code></strong> clause part? </p>
P粉517090748P粉517090748423 days ago618

reply all(2)I'll reply

  • P粉155832941

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

    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.

    reply
    0
  • P粉090087228

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

    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

    reply
    0
  • Cancelreply