Well done! You're really making progress with
OVER(), you can add a
WHERE clause, just as you do in any other query:
AVG(Salary) OVER() AS AvgSalary,
Salary - AVG(Salary) OVER() AS Difference
WHERE DepartmentId = 1;
In this query, we only calculate the salaries in the department with
Id = 1. Two exercises ago, we said that
OVER() with empty brackets means "for all rows in the query result". The "in the query result" part is very important – window functions work only on the rows returned by the query.
In this case, that means we'll get the salary of each IT department employee and the average salary in that department, not the average for the entire company. That's a very important rule to remember. Window functions are always executed after the
WHERE clause, so they work on whatever they find as the result.