PARTITION BY gives each group its own window
PARTITION BY inside OVER splits the rows into separate windows for a calculation. Each drink can keep its own price and also show the average for its category. Coffee prices are compared only with coffee prices; tea prices only with tea prices. Unlike GROUP BY, this keeps every drink's row rather than folding each category into one row. The stored prices stay unchanged.
Starting table: drinks
| name | category | price |
|---|---|---|
| Latte | coffee | 5 |
| Mocha | coffee | 3 |
| Chai | tea | 6 |
A tiny example
In plain English: Show me each drink’s price next to the average price for its category.
SELECT name, category, price, AVG(price) OVER (PARTITION BY category) AS category_avg
FROM drinks
ORDER BY category, name;What comes back
| name | category | price | category_avg |
|---|---|---|---|
| Latte | coffee | 5 | 4 |
| Mocha | coffee | 3 | 4 |
| Chai | tea | 6 | 6 |
Latte and Mocha share the coffee average of 4. Chai is the only tea, so its category average is 6.
Related SQL lessons
Ready for a break?
Take on today's cozy puzzle. A fresh, bite-sized query waits every morning — no timers, no pressure.
play today's sip