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

问题描述

我想在下面的SQL中显示列B，但是当我将其添加到查询中时，出现以下错误:

I would like to display the column B in my below SQL, but when I add it to the query it gives me the following error:

列T2.B' 在选择列表中无效，因为它既不包含在 聚合函数或GROUP BY子句.

Column T2.B' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

我的代码:

SELECT A, COUNT(B) as T1, B 
FROM T2 
WHERE ID=1 
GROUP BY A 

推荐答案

换句话说，此错误告诉您SQL Server不知道要从组中选择哪个 B.

Put in other words, this error is telling you that SQL Server does not know which B to select from the group.

或者您要选择一个特定值(例如MIN，SUM或AVG)，在这种情况下，您将使用适当的聚合函数，或者您希望将每个值都选择为新行(即在GROUP BY字段列表中包含B.

Either you want to select one specific value (e.g. the MIN, SUM, or AVG) in which case you would use the appropriate aggregate function, or you want to select every value as a new row (i.e. including B in the GROUP BY field list).

请考虑以下数据:

ID  A   B
1   1  13
1   1  79
1   2  13
1   2  13
1   2  42

查询

SELECT A, COUNT(B) AS T1 
FROM T2 
GROUP BY A

将返回:

A  T1
1  2
2  3

这一切都很好.

但是请考虑以下(非法)查询，该查询会产生此错误:

However consider the following (illegal) query, which would produce this error:

SELECT A, COUNT(B) AS T1, B 
FROM T2 
GROUP BY A

及其返回的说明问题的数据集:

And its returned data set illustrating the problem:

A  T1  B
1  2   13? 79? Both 13 and 79 as separate rows? (13+79=92)? ...?
2  3   13? 42? ...?

---

但是，以下两个查询清楚地表明了这一点，并且不会导致错误:

---

However, the following two queries make this clear, and will not cause the error:

1. 使用汇总

1. Using an aggregate

SELECT A, COUNT(B) AS T1, SUM(B) AS B
FROM T2
GROUP BY A

将返回:

A  T1  B
1  2   92
2  3   68

将该列添加到GROUP BY列表

SELECT A, COUNT(B) AS T1, B
FROM T2
GROUP BY A, B

将返回:

A  T1  B
1  1   13
1  1   79
2  2   13
2  1   42

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