What's the output looks like?

Question

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??

Answer 1

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

What's the output looks like?

Submit an answer

related questions