When executing with * the compiler gives me an error.
But when running with "emp_no" it runs fine and shows the desired two columns. Shouldn't * be working? showing all the columns?
Hi Oscr!
Thanks for reaching out!
Could you please support your question with the entire code you’ve executed, as well as a screenshot containing the entire error message you’ve encountered? This can help us assist you better. Thank you.
Looking forward to your answer.
Best,
Tsvetelin
I have the same issue, I can show a screenshot of the error below:
The detailed error:
17:43:16 SELECT *, AVG(salary) FROM salaries WHERE salary > 120000 GROUP BY emp_no ORDER BY emp_no LIMIT 0, 1000 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 0.0046 sec
The reason the code isn't working is because the * in the select will give you all columns including from_date and to_date. Since this query contains an aggregation on salary, SQL doesn't know what to do with from_date and to_date in order to add them to the aggregate table. If you add an aggregate function to both of these columns the query should work. Example:
SELECT
emp_no, AVG(salary), MIN(from_date), MAX(to_date)
FROM
salaries
GROUP BY emp_no
HAVING AVG(salary) > 120000;