I am trying to write a query that returns the number of employees by location and the highest paid employee in that location. Employee name, department id and salary are listed in the employees table, and are joined to the cities table by department id. I know this is probably rife with errors, but I am very new to this and trying to learn, so any help would be appreciated. Thanks.
select c.city, count(e.employee_id) , e.high_name
from cities c, employees e
where c.department_id = e.department_id
and high_emp = (select concat(first_name, last_name) as high_name, salary from employees order by salary limit 1);
Query help
Moderator: General Moderators
Re: Query help
You're on the right track, but when you use aggregate functions like COUNT(), you need to use GROUP BY to specify what groups you want to count for.