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 in MS SQL Server

Get to know the data
Get to know the OVER() clause
Computations with OVER()
7. Computations with OVER() – part 1
Aggregate functions with OVER()
Using OVER() with WHERE
Summary

Instruction

Typically, OVER() is used to compare the current row with an aggregate. For example, we can compute the difference between an employee's salary and the average company salary. Why don't we calculate the difference between these two values? Take a look:

SELECT
  FirstName,
  LastName,
  Salary,
  AVG(Salary) OVER() AS AvgSalary,
  Salary - AVG(Salary) OVER() AS Difference
FROM Employee;

The last column shows the difference between an employee's salary and the average salary. That's the typical usage of window functions: to compare the current row with the aggregate of a group of rows. Window functions make it possible to do such comparisons with a simple query.

Exercise

For each employee select their first and last name, their YearsWorked, the average years worked by all employees (AvgYearsWorked), and the difference between each YearsWorked and the average years worked. Name this column Difference .