[Solution] Average Salary for a Manager's Direct Reports

Click here to view the question


Solution:

select
 managers.employee_name
 , [avg(employees.salary):$] as avg_report_salary
from
 employee_data employees
 join employee_data managers on
   employees.manager_id = managers.employee_id
group by
 managers.employee_name

Concepts Covered: Self Joins

Explanation:

Now we can easily write a query where we aggregate the average salary by manager_id, but that doesn’t give us the names of the managers. For the output to be more reader friendly, we need to implement a self join.

In the process of a self join, we create 2 copies of this table. One is named employees, and the other is named managers. We join the managers copy on the criteria that the manager_id in the employees table matches the employee_id of the managers table. In other words, we are joining the manager information using the manager_id field in the original table.

For formatting the currencies, we used a handy Periscope shortcut, but this isn't required for the calculation.

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like Follow
  • 11 mths agoLast active
  • 230Views
  • 1 Following