How does the PARTITION BY clause within a window function differ from GROUP BY, and why is this distinction useful?

Intermediate

Answer

PARTITION BY divides the rows into groups for the purpose of the window function's calculation, similar in concept to GROUP BY, but critically it does not reduce the number of rows returned, meaning you still get one row of output for every input row, just with the window calculation computed separately and correctly for each defined partition.

SELECT ProductCategory, ProductName, Price,
  AVG(Price) OVER (PARTITION BY ProductCategory) AS CategoryAveragePrice
FROM Products;

Real-world example A pricing report shows every individual product alongside the average price for its specific category, using PARTITION BY to calculate that average correctly for each category without collapsing the individual product rows the way GROUP BY would.

Common follow-ups: Can you have multiple different PARTITION BY clauses within the same query for different window functions?;What happens if PARTITION BY is omitted entirely from the OVER clause?

Aggregate Functions & GROUP BY;Data Types & Schema Design

All 7 Window Functions questions