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

Evaluation Order and Common Problems
Window functions and GROUP BY
Summary and Review

Instruction

Okay. As you can see, the query did not succeed. So, how can we select some information for those auctions with a higher-than-average FinalPrice? We have to use a subquery. Take a look:

SELECT
  Id,
  FinalPrice
FROM (
    SELECT
      Id,
      FinalPrice,
      AVG(FinalPrice) OVER() AS AvgFinalPrice
    FROM Auction
  ) C
WHERE FinalPrice > AvgFinalPrice

In the FROM clause, we introduced a subquery that selected the FinalPrice for each auction and the average FinalPrice. Because the whole subquery is calculated before the external query, we can use AvgFinalPrice in the external query.

Exercise

Find the Id, Country, and Views for auctions where the number of views was below the average.

Stuck? Here's a hint!

Create a subquery in the FROM clause with the AVG(Views) OVER() window function.