SELECT list is not in GROUP BY clause and contains nonaggregated column users.id' which is not functionally dependent on columns in GROUP BY

Question

I have some problem with group by and having in same query query

SELECT *
FROM users
WHERE id IN
    (SELECT id
     FROM users
     GROUP BY firstname
     HAVING count(*) > 1)

Getting this error

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'roag.org.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Answer 1

Try this

    SELECT *
FROM users
WHERE firstname IN
    (SELECT firstname
     FROM users
     GROUP BY firstname
     HAVING count(*) > 1)

The reason you get the error is because of the sub query

SELECT id
     FROM users
     GROUP BY firstname
     HAVING count(*) > 1

You are selecting id yet its not included in the group by clause. If you group by id I'm sure you wont get the desired result so group by firstname (and select firstname as well)

Answer 2

You can't use * since you have already use group by. If you table like the following :

a | b | c | d

You you should can group by a:

select a, sum(b), max(c), min(d) group by a;

Think about that, if you group but no sure which field you aggregation, and how can you make the MySQL know you what you want it to understand ? so here just make sure what you really want to do, before aggregation by group.

Answer 3

This is probably because you were misusing the notorious MySQL GROUP BY extension in an older version of MySQL. They disabled it by default in the current version, with sql_mode=only_full_group_by. Read this. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Your subquery is the problem. It looks like you are trying to find all rows with duplicate firstname fields. You should do this.

SELECT *
  FROM users
 WHERE firstname IN
        (SELECT firstname
           FROM users
       GROUP BY firstname
         HAVING count(*) > 1)

Why? Because SELECT id FROM users GROUP BY firstname returns id, which is is not functionally dependent on columns in GROUP BY clause. Aggregation isn't quite as magical as we all wish it were.

Answer 4

ANY_VALUE() is your friend:

SELECT *
FROM users
WHERE id IN
   (SELECT ANY_VALUE(id)
    FROM users
    GROUP BY firstname
    HAVING count(*) > 1)