列的原因在选择列表中无效，因为它不包含在聚合函数或 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' 在选择列表中无效，因为它是不包含在聚合函数或 GROUP BY 子句中.

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 

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

This situation fits into the answer given by Bill Karwin.

以上更正，符合 ExactaBox 的回答 -

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 

<小时>

原始问题 -

对于 SQL 查询 -

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.

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

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

To group all employees that work in the same location we have to first mention the LocationID.

然后，我们不能/不要在其旁边提及每个员工 ID.相反，我们提到了该位置的员工总数，即我们应该 SUM() 在该位置工作的员工.为什么我们采用后一种方式，我不确定.因此，这解释了错误的它不包含在聚合函数中"部分.

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.

GROUP BY 子句部分错误的解释是什么?

What is the explanation for the GROUP BY clause part of the error ?

推荐答案

假设我有下表 T:

a   b
--------
1   abc
1   def
1   ghi
2   jkl
2   mno
2   pqr

然后我执行以下查询:

SELECT a, b
FROM T
GROUP BY a

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

The output should have two rows, one row where a=1 and a second row where a=2.

但是 b 的值应该在这两行的每一行上显示什么?在每种情况下都有三种可能性，查询中没有任何内容明确为每个组中的 b 选择哪个值.这是模棱两可的.

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.

这演示了单值规则，它禁止您在运行 GROUP BY 查询时获得未定义的结果，并且您在选择列表中包含不属于分组的任何列条件，也不会出现在聚合函数(SUM、MIN、MAX 等)中.

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