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: 
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;`
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;

```