Introduction
PARTITION BY
5. Introducing PARTITION BY
Summary

Instruction

With PARTITION BY, you can easily compute the statistics for an entire group while keeping individual row details intact. It goes in the OVER() clause. The basic syntax looks like this:

<WindowFunction> OVER (PARTITION BY Column1, Column2, ..., ColumnN)

Immediately after PARTITION BY, we put the columns we want to use to group the results. PARTITION BY works like GROUP BY in that it puts the rows into groups based on column values. Unlike GROUP BY, PARTITION BY does not collapse rows.

Let's see an example of PARTITION BY at work:

SELECT
  Id,
  Model,
  FirstClassPlaces,
  SUM(FirstClassPlaces) OVER(PARTITION BY Model) as TotalFirstClassPlaces
FROM Train;

For each train, this query returns its Id, Model, FirstClassPlaces and the sum of all first class seats in the same model of train.

Id Model FirstClass
Places
TotalFirst
ClassPlaces
1 InterCity 30 70
2 InterCity 40 70
3 Pendolino 40 135
4 Pendolino 60 135
5 Pendolino 35 135

What functions can you use with PARTITION BY? Aggreagate functions like COUNT(), SUM(), AVG(), etc. work well. Other functions, such as ranking or analytical functions work well too. You'll learn more functions to use with PARTITION BY soon.

Exercise

Run the template and see how it works.