Resolved: S Error code 1055

Question

Whenever i try to do the GROUP BY statement, i keep getting the following response-

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

I am using this code:

SELECT
m.dept_no, m.emp_no, d.dept_name
FROM
dept_manager_dup m
JOIN
departments_dup d ON m.dept_no = d.dept_no
GROUP BY m.emp_no
ORDER BY dept_no;

Please advise on what i am doing wrong?

Answer 1

Hello Joe,

Someone asked this question before and I had the same problem myself. I think the most correct way to resolve this is to run the following query immediately:

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

After you run this code, disconnect and reconnect to the server as you did in a previous exercise. (The last step, reconnecting, was essential for me. It did not work until I reconnected). This is what worked for me.

Alternatively, another user posted an alternative query that will run and produce the desired results:

SELECT
    DISTINCT m.dept_no, m.emp_no, d.dept_name
FROM
    dept_manager_dup m
        JOIN
    departments_dup d ON m.dept_no = d.dept_no
ORDER BY dept_no;

Hope this helps!

Answer 2

Thank you for your reply. I have run the query and it works for me know.

Answer 3

Hi Joe and Zach!
Thanks for reaching out.

@: Zach
Thanks for sharing this piece of information with the Community!

@: Joe
I am glad that you have resolved your issue. As Zach said running the following code and then reconnect to the server solves error 1055.
set @@global.sql_mode := replace(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '');

Hope this helps.
Best,
Tsvetelin

Answer 4

set @@global.sql_mode := replace(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '') did not work for me. I still got the same error. I closed the tab and reopen it, then without running that statement, the transaction worked this time.

Distinct statement worked also.

Answer 5

I have the same query, on asking chat gpt it gave the following code and the results are same. Can you please explain?

SELECT m.emp_no, m.dept_no, d.dept_name
FROM departments_dup d
JOIN dept_manager_dup m ON m.dept_no=d.dept_no
GROUP BY m.emp_no, m.dept_no, d.dept_name
ORDER BY m.dept_no;

Resolved: S Error code 1055

Submit an answer

related questions