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
8. PARTITION BY MULTIPLE COLUMNS
Summary

Instruction

Great! Of course, you can partition rows by multiple columns. Take a look:

SELECT
  route_id,
  ticket.id,
  ticket.price,
  SUM(price) OVER (PARTITION BY route_id, date)
FROM ticket
JOIN journey
ON ticket.journey_id = journey.id;

We wanted to show each ticket with the sum of all tickets on the particular route on the particular date. Neither of the tables would suffice on its own, so we had to join them together to get all the columns.

Exercise

Show the id of each journey, the date on which it took place, the model of the train that was used, the max_speed of that train and the highest max_speed from all the trains that ever went on the same route on the same day.

Stuck? Here's a hint!

Use MAX(max_speed) OVER(PARTITION BY route_id, date).