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 

Answer 1

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
   

Answer 2

Add column in the group by like below:

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

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

If you want to count the field values using groupBy then you can use this code in sequelize:Table.count( { attributes: ['column'], group: 'column', } This will resolve this error Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"