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
This question is related to
sql-server
group-by
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
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
Source: Stackoverflow.com