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

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

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

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