mysqlsqlmysql-error-1055

Error related group by when executing query in mysql

Mysql returning following error when i executing query (with Group BY) for getting the result

Error Code: 1055

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'project.ws_images.wi_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I want to get result with group by MONTH. I have searched about it and found some solution but still facing the issue. I have tried so far

SELECT * FROM `ws_images` WHERE wi_type = 'image' GROUP BY MONTH(date_added);
 SELECT * FROM `ws_images` WHERE wi_type = 'image' GROUP BY DATE_FORMAT(date_added, '%Y%m');

Above both queries returning me same error which i have already mentioned. Can someone guide me where is the issue that i can fix. I would like to appreciate if someone guide me.

enter image description here

Solution

  • You are using a group by without aggregation function .. this behavior in mysql 5.7 is not allowed and you should unset ONLY_FULL_GROUP_BY in sql_mode setting .

    You should esplicitally assign the columns name you need in select clause eventually use distinct (without group by) if you don't need repeated value ..

    otherwise you should explicitally assign the columns and the aggregation function. Be carefull of assign the column not in aggregation function to group by clause