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)
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
PARTITION BY does not collapse rows.
Let's see an example of
PARTITION BY at work:
SUM(FirstClassPlaces) OVER(PARTITION BY Model) as TotalFirstClassPlaces
For each train, this query returns its
FirstClassPlaces and the sum of all first class seats in the same model of train.
What functions can you use with
PARTITION BY? Aggreagate functions like
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.