Common error in Group By
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.
Input: 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.
Input: select empId, sum(empAmount) as debit from emp group by empId Output: 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.
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.