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;

Answer 6

Hi Ramsey Devesh!
Thanks for reaching out.

Please excuse us for the delayed reply. We see the question marked as "Solved" and stumbled upon it rather by chance. Please feel free to post/open separate threads with a reference in case we are being late in answering your questions to speed up our response. (This is something I will note and discuss with the Team in the meantime).

Please refer to my answer in the following thread for an explanation on course structure, as well as where to find the description of error code 1055 and its prevention:

https://learn.365datascience.com/question/error-while-trying-to-replicate-within-lecture-code/

Thank you.

Kind regards,
Martin

Resolved: S Error code 1055

Submit an answer

related questions