列“在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中"

我想在下面的 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?……?

<小时>

但是，以下两个查询说明了这一点，不会导致错误:

1. 使用聚合

   SELECT A, COUNT(B) AS T1, SUM(B) AS B从 T2按 A 分组

   会回来:

   <前>A T1 B1 2 922 3 68

2. 将列添加到GROUP BY列表

   SELECT A, COUNT(B) AS T1, B从 T2按 A、B 分组

   会回来:

   <前>A T1 B1 1 131 1 792 2 132 1 42

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:

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:

1. Using an aggregate

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

   would return:

   A  T1  B
   1  2   92
   2  3   68
   

2. Adding the column to the GROUP BY list

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

   would return:

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