SQL in Oracle HR Schema

Question

I have made a query in Oracle HR schema to see the following information:

1. The city where the department is located
2. The total number of employees in the department

However, the query cannot be executed correctly and said this is "not a GROUP BY expression".

Does anyone knows what's the problem is? Thanks in advance.

SELECT department_name, city, COUNT(employees.department_id)
   FROM departments
       JOIN employees on (departments.department_id=employees.department_id)
         JOIN locations USING (location_id)
GROUP BY department_name;

Answer 1

The problem is you have both aggregated and non-aggregated column (in your case city in the select list.

As I don't know the structure of location table and considering a department have only one location defined you can use max(city),

SELECT department_name, max(city) city, COUNT(employees.department_id) no_of_employees
   FROM departments
       JOIN employees on (departments.department_id=employees.department_id)
         JOIN locations USING (location_id)
GROUP BY department_name;