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

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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] Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

Examples related to sql-server

Examples related to group-by