MySQL Counting Inquiry-CodePudding

whilst I'm still trying to figure this one out I'd like to see your feedback on my code and if you could help me. I'm still trying to grasp the concept of MySQL so any help is much appreciated! I'm running MySQL as an HR

The question requirement is as follows:

Write SQL statement that return the number of employees in every department whose salary is more than 6000. Do not return the department where the average salary is more than 9000

Below is my code (It doesn't work)

SELECT DEPARTMENT_ID, 
       SALARY, 
       COUNT(*) 
FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID
WHERE salary BETWEEN 6000 AND 9000;

My error message: Incorrect syntax near the keyword 'WHERE'. https://gyazo.com/2300fbf0b0ebb43294d599cee276e235 <-- Expected output

Thanks!

CodePudding user response：

Write SQL statement that return the number of employees in every department whose salary is more than 6000. Do not return the department where the average salary is more than 9000

number of employees in every department --- count(EMPLOYEE_ID)

salary is more than 6000 --- WHERE SALARY >6000

Do not return the department where the average salary is more than 9000 ---avg(salary) <=9000

This would be done by:

SELECT DEPARTMENT_ID,
       COUNT(EMPLOYEE_ID) as nr_count
FROM   EMPLOYEES 
WHERE SALARY > 6000
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) <=9000;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6165323c01f188bb0dbb8e6227bac7bc