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 PostgreSQL

Introduction
PARTITION BY
7. Range of OVER(PARTITION BY)
Summary

Instruction

That's right! Remember: window functions only work for those rows which are indeed returned by the query. Take a look at this query:

SELECT
  id,
  model,
  max_speed,
  COUNT(id) OVER (PARTITION BY max_speed)
FROM train
WHERE production_year != 2012;

We cut out the trains with production_year = 2012 and the query would not show them – that's pretty obvious. But the window function would not even count them – we could find out that there are only 2 trains with max_speed = 240, even though there is a third one which was produced in 2012. Note that a GROUP BY clause with a WHERE clause will behave in the same way – GROUP BY will only take into account rows which match the condition(s).

Exercise

Show id, model,first_class_places, second_class_places, and the number of trains of each model with more than 30 first class places and more than 180 second class places.