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

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

第 T2.B 栏’ 在选择列表中无效，因为它不包含在 聚合函数或 GROUPBY 子句。

我的代码:

SELECT A, COUNT(B) as T1, B
FROM T2
WHERE ID=1
GROUP BY A
447772 次浏览
小开
最佳答案

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
小开

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.

小开

You can use case in update and SWAP as many as you want

update Table SET column=(case when is_row_1 then value_2 else value_1 end) where rule_to_match_swap_columns
小开

I have solved this issue by adding the each group by clause which is mentioned in the error when we execute the query.

For eg: let error shows message as--

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

Then I have added table.dealer column in the 'group by' clause as:

Group by table.dealer

Its solve my problem.


提交答案