Hello, I am having difficulties with getting the script you provided working in MySQL. I may be doing something incorrectly but cannot understand what. In doing the exercise, I ran the script below, which works as expected, returning the correct number of results (101): SELECT emp_no, AVG(salary) FROM employees.salaries WHERE salary > 120000 GROUP BY emp_no ORDER BY emp_no; However, when I run either of the two scripts you provided, I get an error. I include below the second of the two scripts for convenience, as this is identical to the working one above, except it should display all columns of the salaries table (plus the AVG(salary)). SELECT *, AVG(salary) FROM employees.salaries WHERE salary > 120000 GROUP BY emp_no ORDER BY emp_no; The error I get from MySQL is as below: "Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.salaries.salary' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" I am running Workbench v.8.0.31. Could this error be due to a change in the compiler rules? Or am I missing something? Thank you in advance for your kind support with this query. Alessandro

Hi Alessandro! Thanks for reaching out. Please, execute the following command: set @@global.sql_mode := replace(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', ''); Then, you have to restart the Workbench in order to save the new settings. Hope this helps. Best, Tsvetelin

Follow this topic

Alessandro Marota

Last answered:

21 Dec 2022

Posted on:

17 Dec 2022

Script for Excercise HAVING not working

in SQL / HAVING

1 answers ( 0 marked as helpful)

Tsvetelin Tsonkov

Instructor

Posted on:

21 Dec 2022

Script for Excercise HAVING not working

Submit an answer

related questions