1

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

Community
  • 1
  • 1
Ayaz Ali Shah
  • 3,453
  • 9
  • 36
  • 68
  • 1
    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 Feb 21 '17 at 12:06
  • Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](http://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) – Sougata Bose Feb 21 '17 at 12:07
  • @GordonLinoff Sorry so highlight my requirement. I need all result group by MONTH – Ayaz Ali Shah Feb 21 '17 at 12:08
  • @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 Feb 21 '17 at 12:13
  • 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 Feb 21 '17 at 12:16

2 Answers2

1

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

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
0

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.

Blank
  • 12,308
  • 1
  • 14
  • 32