Question: 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

```
If you are using a DBMS that has window functions (e.g., SQL Server, Oracle, PostgreSQL), you can do the following:

```
SELECT emp_name, emp_salary - AVG(emp_salary) OVER ( PARTITION BY job_title ) salary_diff
  FROM employee;

```