Hi, I wrote sql query to do exercise 2 as below, SELECT emp_no, from_date FROM dept_emp WHERE from_date > '2000-01-01' GROUP BY emp_no HAVING COUNT(from_date) > 1 ORDER BY emp_no; but the MySQL Workbench keeps saying there is error code 1055, expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ~ so I fixed the error writing query by adding 'from_date' as two columns that I used in SELECT query as below instead of the above, SELECT emp_no , from_date FROM dept_emp WHERE from_date > '2000-01-01' GROUP BY emp_no , from_date HAVING COUNT(from_date) > 1 ORDER BY emp_no; and I've got 0 row(s) returned. Is this the result correct? or the way you guys designed to??

Hi Yenny! Thanks for reaching out. The query gives 0 rows because you used two columns in the GROUP BY clause. So, the solution is not to change the code but to apply some new settings. Depending on your operating system and version of MySQL, you will be working with different SQL settings. To make sure you can take some of the remaining lectures of the course without unnecessary interruption, we strongly advise you to execute the following query now. 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

YennyChung

Last answered:

06 Jul 2022

Posted on:

06 Jul 2022

What's the output looks like?

in SQL / WHERE vs HAVING - Part II

1 answers ( 0 marked as helpful)

Tsvetelin Tsonkov

Instructor

Posted on:

06 Jul 2022

What's the output looks like?

Submit an answer

related questions