Open In App

Common error in Group By

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

Prerequisite: SQL | GROUP BY

Error:
Column ‘col’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Emp Sample Table:



Examples:

Input: 
select empId, empName, empAmount from emp group by empId, empName

Output: Error 

Explanation:
empAmount is not in group by list and is neither an aggregate function, the query will give above error. For empId and empName there are multiple empAmount, Database engine could not choose one empAmount.

Input: 
select empId, sum(empAmount) as debit from emp group by empId

Output:
empId | debit
1     | 300
2     | 400 

GROUP BY:
From the above examples it is clear that we can have only those columns which are there in the group by or arguments of an aggregate function in the select clause. but that’s not completely true.

“The 1999 and 2003 versions of the SQL standard require that the columns appearing in the SELECT list are functionally dependent upon the groups defined by the GROUP BY clause. In other words, if we know that a column contains only one value for any given combination of values in the columns appearing in the GROUP BY clause, we may reference the column in the SELECT list even if it does not appear in an aggregate expression”–By Roland Bouman

In layman terms:

If columns in group by has a column which is either the primary key or unique key then the combination will only have one value for other columns.

But grouping by a unique column does not make sense but it will become useful when other tables are involved.

A Few Exceptions:
MySQL lets you SELECT anything in a query with group by. It will select in random order and whatever is first will return you.

Example:

Input:
select empId, empName, empAmount from emp group by empId, empName 

The above query will not give any error. It will pick random empAmount from the list and pair it with the empID and empName.

The correct behavior can be enabled by adding a flag (i.e., ONLY_FULL_GROUP_BY) or by using MySQL v5.7.5 or higher. In that case, the query would raise the above error.


Last Updated : 19 Jun, 2019
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads