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.
- SQL | GROUP BY
- Difference between Where and Group By
- How to implement SQL GROUP BY in Java?
- MySQL | Recursive CTE (Common Table Expressions)
- Count the number of a special day between two dates by using PL/SQL
- MySQLi Procedural Functions
- MySQL | CONNECTION_ID( ) Function
- MySQL | CAST( ) Function
- MySQL | SESSION_USER( ) Function
- MySQL | COALESCE( ) Function
- MySQL | IF( ) Function
- MySQL | BINARY Function
- MySQL | CONV( ) Function
- MySQL | CONVERT( ) Function
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. 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.