PARTITION BY & window aggregates
Show detail alongside its group total
Lesson 16 of 20 · 13 min · Module 5: Explain a more complex report
Sometimes a reader needs an individual value and its category total on the same row. A partitioned window aggregate can provide both.
Start this lesson →Free. No installation: you write and run the code in your browser, and it is checked automatically.
By the end, you’ll be able to…
- Use PARTITION BY to choose comparison groups
- Distinguish windows from grouped results
The idea
SUM(xp) OVER (PARTITION BY language) calculates one total for each language group and repeats the appropriate total alongside each learner.
SELECT name, language, xp,
SUM(xp) OVER (PARTITION BY language) AS language_xp
FROM learners;01Define the comparison set
Alex and Jordan share a language partition, so each receives the JavaScript total of 210. Their individual XP values remain distinct.
02Preserve the intended row meaning
GROUP BY language would usually produce one row per language. This report needs one row per learner, so the window aggregate is a better fit.
03Avoid an unintended running total
An ORDER BY added inside this SUM window changes the default frame behavior. Omit it when you want the full partition total on every row.
Your turn
What the checks look for
- Keeps learner detail and attaches the right language totals
Take this with you
Choose the calculation that matches what one output row should represent.
Go a little further
Add an XP percentage per language with numeric division and compare each partition’s percentages.