列的原因在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中 [英] Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

问题描述

可能存在重复：

我得到一个错误 -

列'Employee.EmpID'在选择列表中无效，因为它不包含

---

 选择loc.LocationID，emp.EmpID $ b $ from Employee作为emp完全加入位置作为loc 
 on emp.LocationID = loc.LocationID 
 group by loc.LocationID 
  

这种情况符合Bill Karwin给出的答案。

修正以上，适合ExactaBox回答 -

  select loc.LocationID，count（emp.EmpID） - 不计数（*），不希望将Employee的空位
视为emp fu将位置作为loc 
加入emp.LocationID = loc.LocationID 
 group by loc.LocationID 
  

原始问题 -

对于SQL查询 -

 从Employee中选择* 
作为emp完全加入位置为loc 
 on emp.LocationID = loc.LocationID 
 group by（loc.LocationID）
  

我不明白为什么会出现此错误。我想要做的就是加入表格，然后将所有员工聚集在一起。

我想我对自己的问题有一个部分解释。告诉我是否可以 -

要将在同一地点工作的所有员工分组，我们必须首先提及LocationID。

然后，我们不能/不会提及它旁边的每个员工ID。相反，我们提到该位置的雇员总数，即我们应该SUM（）在该位置工作的雇员。为什么我们以后一种方式来做，我不确定。
因此，这解释了它不包含在任何一个集合函数中的部分错误。

GROUP BY 子句的部分错误？

解决方案

假设我有以下表格 T ：

  ab 
  - ------- 
 1 abc 
 1 def 
 1 ghi 
 2 jkl 
 2 mno 
 2 pqr 
  

我做了以下查询：

  SELECT a，b 
 FROM T 
 GROUP BY a 
  

输出应该有两行，一行是 a = 1 ，第二行是 a = 2 。

但是这两行的每一行应该显示 b 的值？每种情况下都有三种可能性，查询中没有任何内容可以明确每个组中为b选择哪个值。这是不明确的。

这演示了单值规则，它禁止您在运行GROUP BY查询时得到的未定义结果，而您包括选择列表中既不属于分组标准，也不出现在聚合函数（SUM，MIN，MAX等）中的任何列。

修复它可能看起来像这样：

  SELECT a，MAX（b）AS x 
 FROM T 
 GROUP BY a 
  

现在很明显，您需要以下结果：

  ax 
 -------- 
 1 ghi 
 2 pqr 
  

Possible Duplicate:
GROUP BY / aggregate function confusion in SQL

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 ?

解决方案

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

这篇关于列的原因在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！