0

I'm trying to select all columns from a database table and group it by one special column:

SELECT * FROM `table` GROUP BY `email`

But I get an error:

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'gassupplytool.ggg_nitrogen.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I have done it for years this way without getting any problems. In this case I am working on a foreign server. Can anyone help?

cypher75
  • 2,099
  • 4
  • 18
  • 18
  • 1
    The duplicate link explains what you can do to make your MySQL behave the way did previously. The jist here is that MySQL has a server mode called `ONLY_FULL_GROUP_BY` which, if enabled, will not allow a query like the one you wrote, which selects columns not appearing in the `GROUP BY` clause (or in an aggregate function). – Tim Biegeleisen Oct 18 '17 at 09:27
  • 1
    I was going to answer, but this question literally gets asked here once per day, and I'm certain it is already well covered here on Stack Overflow. – Tim Biegeleisen Oct 18 '17 at 09:27
  • OK, thanks Tim. Sorry for duplicate, but I didn't knew what to search for :-) – cypher75 Oct 18 '17 at 09:34
  • "I have done it for years this way without getting any problems" Oh dear. We can only hope you haven't worked on anything important :-( – Strawberry Oct 18 '17 at 10:04
  • @strawberry: many major customer projects. What is wrong with this? Using mysql.com as reference... – cypher75 Oct 18 '17 at 10:11
  • https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – Strawberry Oct 18 '17 at 10:17
  • http://sqlfiddle.com/#!9/0a74d4/2 - I guarantee that you have done this, or something like it. – Strawberry Oct 18 '17 at 10:39
  • Using group by with the imprecise mysql only syntax provides "approximations", or worse, in the columns not specified by the group by clause. It is imprecise and a long way from good practice. You should use ONLY_FULL_GROUP_BY and queries that comply with it. – Paul Maxwell Oct 19 '17 at 02:32

0 Answers0