Error related group by when executing query in mysql

1
votes

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

2
mysqlsqlmysql-error-1055
The use of select * with group by shows a lack of understanding of SQL. You should provide both sample data and desired results, because your queries make no sense at all. – Gordon Linoff
Possible duplicate of Error related to only_full_group_by when executing a query in MySql – Sougata Bose
@GordonLinoff Sorry so highlight my requirement. I need all result group by MONTH – Ayaz Ali Shah
@Mr.Developer . . . group by MONTH doesn't make sense when you want all the other columns. group by is usually used with aggregation functions. – Gordon Linoff
If you want to group results visually in the output, you should use ORDER BY instead and group the results as you like when you build the view. GROUP BY in sql does not do what you think it does. – jeroen

2 Answers

1
votes

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

0
votes

In mysql, you should set sql_mode without ONLY_FULL_GROUP_BY to do the query you mentioned in op. Or you only can select columns in group by clause, and some aggregational columns.