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

问题描述

我想在我的下面的SQL中显示列 B ，但是当我将它添加到查询时，它给了我以下错误：

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

我的代码：

<$ p $ （B）作为T1，B
从T2
WHERE ID = 1
GROUP BY A
换句话说，这个错误告诉你SQL Server不知道哪个 / em> B 以从组中选择。

要么选择一个特定的值（例如 MIN ， SUM 或 AVG ），在这种情况下，您将使用适当的聚合函数，或者您希望将每个值选择为新行（即包括<$ c










考虑以下数据：



 
 ID AB 
 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 
 
这一切都很好。然而，请考虑以下（非法）查询，这会产生此错误：
  SELECT A，COUNT（B）AS T1，B 
 FROM T2 
 GROUP BY A 
  
其返回的数据集说明问题：
 
 
 
 A T1 B 
 1 2 13？ 79？ 13和79都是分开的行吗？ （13 + 79 = 92）？ ...？ 
 2 3 13？ 42？ ...？ 
 
 
 
 
 
 
 
然而，以下两个查询清楚地表明了这一点，并且不会导致错误：  
 
 
 
 
 
使用汇总 
 
 
  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  P> 
 
 
  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 
 
 
 
 
 
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:

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

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



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