I'm unable to group the query by its distinct values

Question

This is the error that I am getting

Error Code: 1055. Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.d.dept_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This is dit-dot same code used by my instructor

SELECT
    m.dept_no, m.emp_no, d.dept_name

FROM

    dept_manager_dup m

LEFT JOIN

    departments_dup d  ON m.dept_no = d.dept_no

GROUP BY 
    m.emp_no

ORDER BY 
    m. dept_no;

He was able to execute it but I couldn't. I am getting the same error all the time.

I said the same to the instructor so he insisted me to run this code and restart MySQL.

INSTRUCTOR SUGGESTION to solve the error

set @@global.sql_mode := replace(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '');

I also did the same but nothing changed and got the same errors again and again.

Could someone please help me in getting clarity about it I've just started to learn this sql which is very important to me.

Hope I will get a appropriated solution for my issue. Thank you

I've gone ahead and removed the conflicting tags here; please only tag the RDBMS you are *really* using. Tagging multiple conflicting technologies makes your question unclear and thus *harder* to answer. Please [edit] your question to retag the correct RDBMS. — Thom A, Mar 27 '23 at 14:40
Why are you using a `GROUP BY` at all here? You have no aggregation. — Thom A, Mar 27 '23 at 14:41
Provided code is equivalent of `SELECT m.dept_no, ANY_VALUE(m.emp_no) AS emp_no , ANY_VALUE(d.dept_name) AS dept_name FROM ...` . Question is what is the point of choosing arbitrary value? — Lukasz Szozda, Mar 27 '23 at 14:44
Looks like `ONLY_FULL_GROUP_BY ` mode is enabled. Can you update group by to : `GROUP BY m.dept_no, m.emp_no, d.dept_name` and let us know; if you still face error — Tushar, Mar 27 '23 at 14:46
Try `SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));` and try you select statement without restarting the database. Read more about your problem in this answer https://stackoverflow.com/a/36033983/3604523 . Besides I totally agree with @Thom A's comment: why are you using group by? — h.m.i.13, Mar 27 '23 at 15:26
Re "he insisted me to run this code and restart MySQL" that's not good. Running that code will change the settings for all new database connections, but the effect will only last *until* you restart MySQL. Use `set persist sql_mode=` to make it last across restarts. (Though Bill is right, you really do want to leave the setting on. It is on by default in modern versions for very good reasons, and with it off MySQL will act differently than every other major database system) — ysth, Mar 27 '23 at 15:34

Bill Karwin · Answer 1 · 2023-03-27T15:29:27.957

Changing the sql_mode to remove ONLY_FULL_GROUP_BY is not a good solution. This mode is important to prevent ambiguous queries. For the reasons, read:

My answer to Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

In your case, you may correct the issue this way:

SELECT
    MAX(m.dept_no) AS dept_no, m.emp_no, MAX(d.dept_name) AS dept_name

FROM

    dept_manager_dup m

LEFT JOIN

    departments_dup d  ON m.dept_no = d.dept_no

GROUP BY 
    m.emp_no

ORDER BY 
    dept_no;

Or alternatively:

SELECT
    m.dept_no, m.emp_no, d.dept_name

FROM

    dept_manager_dup m

LEFT JOIN

    departments_dup d  ON m.dept_no = d.dept_no

GROUP BY 
    m.emp_no, m.dept_no, d.dept_name

ORDER BY 
    d.dept_no;

I'm unable to group the query by its distinct values

1 Answers1