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

Introduction

Ensure all non-aggregated columns are included in the GROUP BY clause to avoid errors in T-SQL queries with aggregate functions.

Description

When you use aggregate functions like SUM , AVG , or COUNT in a query, any column or expression that is not aggregated must be included in the GROUP BY clause. Failure to do so will result in runtime errors as SQL Server cannot determine how to group the non-aggregated columns.

For example:

-- Example of problematic query
SELECT column1, COUNT(column2) FROM TableName;

This query is problematic because column1 is not part of an aggregate function or included in a GROUP BY clause, leading to an error.

Runtime errors are raised when non-aggregated columns are missing from the GROUP BY clause.
This mistake leads to logical errors in data summarization, producing incorrect results.

How to fix

Ensure that all non-aggregated columns in your query are included in the GROUP BY clause to prevent errors and ensure correct data summarization.

Follow these steps to address the issue:

1.Identify all columns in your query that are not part of an aggregate function.

2.Include each of these non-aggregated columns in the GROUP BY clause of your query.

3.Verify the query to ensure that only the intended columns are being aggregated, and the grouping is logical and correct.

For example:

-- Example of corrected query
SELECT column1, COUNT(column2)
FROM TableName
GROUP BY column1;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

5 minutes per issue.

Additional Information

SELECT - GROUP BY- Transact-SQL

Example Test SQL

SELECT MAX(compatibility_level),  collation_name + name as n , some_missing_column + 1, 1 + 1
FROM sys.databases
GROUP BY name,collation_name

SELECT MAX(compatibility_level), collation_name + name as n , some_missing_column + 1, 1 + 1
FROM sys.databases
GROUP BY collation_name + name

SELECT MAX(database_id + compatibility_level), name + collation_name as n   FROM sys.databases
GROUP BY NAME + collation_name

SELECT MAX(database_id + compatibility_level), name + collation_name as n   FROM sys.databases
GROUP BY  collation_name + name

Analysis Results

	Message	Line	Column
1	SA0262 : Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.	1	63
2	SA0262 : Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.	5	62
3	SA0262 : Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.	12	47

Analysis Rules