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

Window functions and GROUP BY
Summary and Review

Instruction

Good job. Before, we said that window functions were calculated after the GROUP BY clause. This has a very important implication for our queries: if the query uses any aggregates, such as GROUP BY or HAVING, the window function sees the group rows instead of the original table rows.

To get a better understanding of this phenomenon, take a look at the following example:

SELECT
  CategoryId,
  FinalPrice,
  AVG(FinalPrice) OVER() AS AvgFinalPrice
FROM Auction;

This simple query will show the Id and FinalPrice of each auction alongside the average FinalPrice from all the auctions. Now, take a look at the modified example with grouping:

SELECT
  CategoryId,
  MAX(FinalPrice) AS MaxFinalPrice,
  AVG(FinalPrice) OVER() AS AvgFinalPrice
FROM Auction
GROUP BY CategoryId;

Will this query work? Let's find out!

Exercise

Try to run the template query from the example.

As you can see, the query doesn't work. This is because we can't use the column FinalPrice in the window function. Once the rows have been grouped, there is no FinalPrice value that makes sense for all the rows together.