MYSQL: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Hey guys,

Recently when we run one of our query with group by . we encountered an MYSQL error as like below:

ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column <column name> which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

When we dig in deeper, we came to know that we have enabled only_full_group_by  which is by default in MySQL 5.7.5 and later. 

For example. Query

select name , phone_number, count(*)  from contact 

group by phone_number ;

contact table :

----------------------

  name     phone_number

   Raj       123

   Ram     123 

   Karan    456

See above table has 2 names pointing to same number 123 . so when we group by the data with phone_number it tries to count no . of records based on phone_number.

Now when we keep the name in the selection list . MYSQL has to pick only one name to show to user . 

System will confuse which name to pick Raj / Ram . 

so , when we enable only_full_group_by . we are letting mysql to not accept queries with functional independence in the selection list.  if they are functionally dependent or primary key then it will work. 

To overcome above problem :  2 solutions

Solution 1: 

   Disable only_full_group_by . follow below link 

     https://stackoverflow.com/questions/23921117/disable-only-full-group-by

Solution 2:

    ANY_VALUE()  function for non-agggregated columns in the selection list. By using this, system picks any one name out of multiple values. 

     select any_value(name) , phone_number, count(*)  from contact 

group by phone_number ; 

Please don't keep the non-aggregated columns in selection list at any cost. If you think its necessary then follow above steps to overcome your problem 

More on only_full_group_by

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html