Find the average level of departmental salary

Question

I have two tables

db_employee (id,first_name,last_name,salary,department_id)

db_dept (department_id,department)

Here is some sample data

db_employee

id - fist_name - last_name - salary - department_id

10301-Keith-Morgan-27056-2
10302-Tyler-Booth-32199-3


db_dept

id-department

2 - Human Resources
3 - Operations

I want to output a table that displays employees, their salaries, and the average salary for the employee's department.

I tried using a subquery to find the average salary of the department before doing the outer query, but I ran into an error.

Select
    first_name,
    last_name,
    salary,
    (
        select
            avg(emp.salary),
            dep.department
        from db_employee emp
        join db_dept dep on emp.department_id=dep.id
        group by dep.department
    ) As avgsaldepartment
from db_employee

Answer 1

SELECT 
emp.first_name,
emp.last_name,
salary,
demp.avg_salary
FROM db_employee emp
  INNER JOIN db_dept dep ON emp.department_id=dep.id
  INNER JOIN (
     SELECT
     AVG(salary) avg_salary,
     department
     FROM db_employee
       INNER JOIN db_dept ON department_id=id ) demp 
           ON demp.department=dep.department