Skip to content
Related Articles
Get the best out of our app
Open App

Related Articles

Common error in Group By

Improve Article
Save Article
Like Article
Improve Article
Save Article
Like Article

Prerequisite: SQL | GROUP BY

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:


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

Output: Error 

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.

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

empId | debit
1     | 300
2     | 400 

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.


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.

My Personal Notes arrow_drop_up
Last Updated : 19 Jun, 2019
Like Article
Save Article
Similar Reads