SQL Server——列“在select列表中无效，因为它不包含在聚合函数或GROUP BY子句中”

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:

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

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

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

My code:

我的代码:

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

2 个解决方案

#1

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

换句话说，这个错误告诉您SQL Server不知道从组中选择哪个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).

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

---

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
   

#2

The consequence of this is that you may need a rather insane-looking query, e. g.,

这样做的结果是，您可能需要一个相当疯狂的查询，例如，

SELECT [dbo].[tblTimeSheetExportFiles].[lngRecordID]            AS lngRecordID
          ,[dbo].[tblTimeSheetExportFiles].[vcrSourceWorkbookName]  AS vcrSourceWorkbookName
          ,[dbo].[tblTimeSheetExportFiles].[vcrImportFileName]      AS vcrImportFileName
          ,[dbo].[tblTimeSheetExportFiles].[dtmLastWriteTime]       AS dtmLastWriteTime
          ,[dbo].[tblTimeSheetExportFiles].[lngNRecords]            AS lngNRecords
          ,[dbo].[tblTimeSheetExportFiles].[lngSizeOnDisk]          AS lngSizeOnDisk
          ,[dbo].[tblTimeSheetExportFiles].[lngLastIdentity]        AS lngLastIdentity
          ,[dbo].[tblTimeSheetExportFiles].[dtmImportCompletedTime] AS dtmImportCompletedTime
          ,MIN ( [tblTimeRecords].[dtmActivity_Date] )              AS dtmPeriodFirstWorkDate
          ,MAX ( [tblTimeRecords].[dtmActivity_Date] )              AS dtmPeriodLastWorkDate
          ,SUM ( [tblTimeRecords].[decMan_Hours_Actual] )           AS decHoursWorked
          ,SUM ( [tblTimeRecords].[decAdjusted_Hours] )             AS decHoursBilled
      FROM [dbo].[tblTimeSheetExportFiles]
      LEFT JOIN   [dbo].[tblTimeRecords]
              ON  [dbo].[tblTimeSheetExportFiles].[lngRecordID] = [dbo].[tblTimeRecords].[lngTimeSheetExportFile]
        GROUP BY  [dbo].[tblTimeSheetExportFiles].[lngRecordID]
                 ,[dbo].[tblTimeSheetExportFiles].[vcrSourceWorkbookName]
                 ,[dbo].[tblTimeSheetExportFiles].[vcrImportFileName]
                 ,[dbo].[tblTimeSheetExportFiles].[dtmLastWriteTime]
                 ,[dbo].[tblTimeSheetExportFiles].[lngNRecords]
                 ,[dbo].[tblTimeSheetExportFiles].[lngSizeOnDisk]
                 ,[dbo].[tblTimeSheetExportFiles].[lngLastIdentity]
                 ,[dbo].[tblTimeSheetExportFiles].[dtmImportCompletedTime]

Since the primary table is a summary table, its primary key handles the only grouping or ordering that is truly necessary. Hence, the GROUP BY clause exists solely to satisfy the query parser.

由于主表是一个汇总表，它的主键只处理真正必要的分组或排序。因此，GROUP BY子句的存在仅仅是为了满足查询解析器。