问题描述
我想在下面的 SQL 中显示列 B，但是当我将它添加到查询中时，它给了我以下错误:
T2.B' 列在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句.
我的代码:
SELECT A, COUNT(B) as T1, B从 T2哪里 ID=1按 A 分组解决方案换句话说，这个错误告诉你SQL Server不知道哪个
B从组中选择.无论您想选择一个特定的值(例如
<小时>MIN、SUM或AVG)，在这种情况下，您都可以使用适当的聚合函数，或者您想选择每个值作为新行(即在GROUP BY字段列表中包含B).考虑以下数据:
<前>身份证号1 1 131 1 791 2 131 2 131 2 42查询
SELECT A, COUNT(B) AS T1从 T2按 A 分组会回来:
<前>一个 T11 22 3一切都很好.
但是，请考虑以下(非法)查询，这会产生此错误:
SELECT A, COUNT(B) AS T1, B从 T2按 A 分组及其返回的说明问题的数据集:
<前>A T1 B1 2 13?79?13 和 79 作为单独的行?(13+79=92)?……?2 3 13?42?……?<小时>
但是，以下两个查询说明了这一点，不会导致错误:
使用聚合
SELECT A, COUNT(B) AS T1, SUM(B) AS B从 T2按 A 分组会回来:
<前>A T1 B1 2 922 3 68
将列添加到GROUP BY列表
SELECT A, COUNT(B) AS T1, B从 T2按 A、B 分组会回来:
<前>A T1 B1 1 131 1 792 2 132 1 42I 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:
Column T2.B' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
My code:
SELECT A, COUNT(B) as T1, B
FROM T2
WHERE ID=1
GROUP BY A
Put in other words, this error is telling you that SQL Server does not know which B to select from the group.
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).
Consider the following data:
ID A B 1 1 13 1 1 79 1 2 13 1 2 13 1 2 42
The query
SELECT A, COUNT(B) AS T1
FROM T2
GROUP BY A
would return:
A T1 1 2 2 3
which is all well and good.
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:
Using an aggregate
SELECT A, COUNT(B) AS T1, SUM(B) AS B FROM T2 GROUP BY Awould return:
A T1 B 1 2 92 2 3 68
Adding the column to the
GROUP BYlistSELECT A, COUNT(B) AS T1, B FROM T2 GROUP BY A, Bwould return:
A T1 B 1 1 13 1 1 79 2 2 13 2 1 42
这篇关于列“在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！