Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions

Get to know tables
OVER()
Computations with OVER()
Aggregate functions with OVER()
OVER() and WHERE
12. Range of OVER()
Summary

Instruction

Alright. Of course, you can add a WHERE clause just as you do in any other query:

SELECT
  first_name,
  last_name,
  salary,
  AVG(salary) OVER(),
  salary - AVG(salary) OVER()
FROM employee
WHERE department_id = 1;

Now, we only calculate the salaries in the department with id = 1. Two exercises ago, we said that OVER() means 'for all rows in the query result'. This 'in the query result' part is very important – window functions work only on the rows returned by the query.

Here, this means we'll get the salary of each IT department employee and the average salary in that department, and not in the entire company.That's a very important rule which you need to remember. Window functions are always executed AFTER the WHERE clause, so they work on whatever they find as the result.

Exercise

Show the first_name, last_name and salary of every person who works in departments with id 1, 2 or 3, along with the average salary calculated in those three departments.

Stuck? Here's a hint!

In order to pick department_id 1, 2 or 3, you can use IN(…) in the WHERE clause, or a few conditions joined with OR.