Calculates the difference between employee salary and the average salary of job group

Given a table like:

Employee (emp_no, emp_fname, emp_lname, emp_salary, job_title) 

How to write a query that for each employee calculates the difference between his/her salary to the average salary of his/her job group?

To get the avg. Salary for each group I use:

Select job_title, avg(emp_salary) as avg_salary 
from employee group by job_title;

But I’m struggling to find the difference between salary and avg. salary for each job title.

Answer

Depending on which database you are using, you may be able to arrive at this more efficiently using either window functions or CTEs. But a SQL solution that should work on pretty much any SQL-based database you could use would look like:

    SELECT a.emp_no,
           a.emp_fname, 
           a.emp_lname, 
           a.job_title, 
          (a.emp_salary - b.emp_salary) as salary_difference 
FROM employee AS a
INNER JOIN (
     SELECT job_title, avg(emp_salary)as emp_salary from employee group by    
             job_title) as b
    ON a.job_title = b.job_title