Last answered:

06 Jun 2023

Posted on:

15 Mar 2023

1

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

3 answers ( 1 marked as helpful)
Instructor
Posted on:

17 Mar 2023

1

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

Posted on:

09 May 2023

0

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



Posted on:

06 Jun 2023

2

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;

Submit an answer