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

The topic describes the SA0262 analysis rule.

Message

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

Description

The rule checks T-SQL code for queries using aggregate functions and not having all non-aggregated columns or column-expressions specified in the GROUP BY list.

Each column in any non-aggregate expression in the select list or the expression itself must be included in the GROUP BY list. Otherwise an error will be generated when the query is executed.

How to fix

Make sure that all columns and column expressions that are not used in an aggregate function are specified in the GROUP BY list.

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