CODEWITHSUNDEEP
sql-servergroup-by
msvuze
msvuze

Reputation: 1397

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

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

Upvotes: 114

Views: 553181

Answers (6)

wajahat12345
wajahat12345

Reputation: 27

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"

Upvotes: 0

Abhishek Mishra
Abhishek Mishra

Reputation: 43

Add column in the group by like below:

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

Upvotes: 2

Gaur 97
Gaur 97

Reputation: 93

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.

Upvotes: 0

Viswanath Lekshmanan
Viswanath Lekshmanan

Reputation: 10093

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

Upvotes: 1

David A. Gray
David A. Gray

Reputation: 1075

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.

Upvotes: 0

lc.
lc.

Reputation: 116538

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

Upvotes: 204

Related Questions