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
5. What is OVER()?
Computations with OVER()
Aggregate functions with OVER()
Using OVER() with WHERE
Summary

Instruction

Okay, let's get started with OVER().

Obviously, OVER(...) changes based on what you put inside the brackets. At its most basic, the brackets are left empty, like this: OVER(). This means that the window consists of all the rows in the query. Take a look at the example below:

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

It's pretty easy to understand, but take a look at let's focus on the last column:

AVG(Salary) OVER()

What might this mean? AVG(Salary) means we're looking for the average salary. What rows are we including in this function? All of them, because OVER() with empty brackets means "for all rows in the query result". In other words, we're looking for the average salary in the entire company.

Note that we did NOT group rows. OVER() allows us to show the details of single rows as well as the result of the aggregating function. That wouldn't be so easy with GROUP BY – we would have to write a subquery, which is more complicated and less effective. OVER() makes our work simple and efficient.

Exercise

Now it's your turn to write a window function. For each employee, find their first name, last name, and salary. Also show the sum of all salaries in the company. Name the last column TotalSalary.

Note that the last column is an aggregated column, even though you're not using GROUP BY.