Click or drag to resize

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.

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.

Categories

Design Rules, New Rules

Additional Information
Example Test Script
SQL
 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

 MessageLineColumn
1SA0262 : Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.163
2SA0262 : Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.562
3SA0262 : Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.1247
See Also

Other Resources