search

Home  >  Q&A  >  body text

Find the average level of departmental salary

<p>I have two tables</p> <pre class="brush:php;toolbar:false;">db_employee (id,first_name,last_name,salary,department_id) db_dept (department_id,department)</pre> <pre class="brush:php;toolbar:false;">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</pre> <p>I want to output a table that displays employees, their salaries, and the average salary for the employee's department. </p> <p>I tried using a subquery to find the average salary of the department before doing the outer query, but I ran into an error. </p> <pre class="brush:php;toolbar:false;">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</pre></p>
P粉329425839P粉329425839446 days ago614

reply all(1)I'll reply

  • P粉742550377

    P粉7425503772023-09-03 14:10:02

    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

    reply
    0
  • Cancelreply