Find Average Department Salary-CodePudding

I have two tables

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

db_dept (department_id,department)

Here are same 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 - operation

I'm trying to output a table that shows employee, their salary and the average salary for that employee's department.

I tried doing a subquery to find the department avg salary first. Then do an outer query but I am getting 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

CodePudding user response：

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

CodePudding user response：

SELECT 
emp.first_name,
emp.last_name,
emp.salary,
AVG(emp.salary) OVER ( PARTITION BY dep.id) as avgsalarydep
  FROM db_employee emp
       INNER JOIN db_dept dep ON department_id=id