Fix error: SELECT list is not in GROUP BY clause, SELECT list contains nonaggregated column, incompatible with sql_mode=only_full_group_by, In aggregated query without GROUP BY

If you are getting following error in mysql query:

#1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

#1140 – In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column. this is incompatible with sql_mode=only_full_group_by

This type of error is thrown when ONLY_FULL_GROUP_BY sql mode is enabled (which it is by default).

You can fix this error by disabling ONLY_FULL_GROUP_BY sql mode. This is a two step approach.

Step 1: check existing sql_mode

select @@global.sql_mode;
select @@session.sql_mode;

Step 2:​​ ​ Disable sql_mode

Let’s assume that select query to get enabled sql_mode return ONLY_FULL_GROUP_BY​ , STRICT_TRANS_TABLES​ , ​ NO_ZERO_IN_DATE​ , ​ NO_ZERO_DATE​ , ERROR_FOR_DIVISION_BY_ZERO​ , NO_ENGINE_SUBSTITUTION and we want to disable ONLY_FULL_GROUP_BY. Simply remove this from and leave other enabled modes as it is.

set global sql_mode='STRICT_TRANS_TABLES​ , ​ NO_ZERO_IN_DATE​ , ​ NO_ZERO_DATE​ , ERROR_FOR_DIVISION_BY_ZERO​ , NO_ENGINE_SUBSTITUTION';

set session sql_mode='STRICT_TRANS_TABLES​ , ​ NO_ZERO_IN_DATE​ , ​ NO_ZERO_DATE​ , ERROR_FOR_DIVISION_BY_ZERO​ , NO_ENGINE_SUBSTITUTION';

You can disable any sql_mode whichever you want. Or if you want to disable all sql_modes you can set it to blank as shown in query below:

set global sql_mode='';

set session sql_mode='';

Note: This change in sql_mode is temporary. If you restart the server or mysql service, it’s default values will be set again and every time when your service is restarted, you have to reset sql_mode variable. To make permanent changes you can read out sql mode in mysql.

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top