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

```
Alternatively, you can also use a subquery to get the answer to this question as below.

Step1: Create a query to get the average of the employee's salary.

Step2: Use query created in step-1 as a subquery and subtract it from the main query's
salary column.

Code :
`SELECT first_name,last_name,salary ,salary-(SELECT AVG(salary) FROM employees) AS 'SalDiff' FROM employees;`