Common error in Group By

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.



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.