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

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:

SQL
Copy
1-- Example of problematic query
2SELECT 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:

SQL
Copy
1-- Example of corrected query
2SELECT column1, COUNT(column2) 
3FROM TableName
4GROUP 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.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1SELECT MAX(compatibility_level),  collation_name + name as n , some_missing_column + 1, 1 + 1  
 2FROM sys.databases 
 3GROUP BY name,collation_name
 4
 5SELECT MAX(compatibility_level), collation_name + name as n , some_missing_column + 1, 1 + 1  
 6FROM sys.databases 
 7GROUP BY collation_name + name
 8
 9SELECT MAX(database_id + compatibility_level), name + collation_name as n   FROM sys.databases 
10GROUP BY NAME + collation_name 
11
12SELECT MAX(database_id + compatibility_level), name + collation_name as n   FROM sys.databases 
13GROUP 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
See Also

Other Resources

Analysis Rules