OVER calculates across rows without collapsing them
GROUP BY folds rows into one row per group — handy, but the original rows disappear. Adding OVER () to an aggregate like AVG or SUM turns it into a window function: it still calculates across rows, yet every original row stays on the page, with the result repeated beside each one. Perfect when you want to compare each row to a bigger picture, like each drink's price next to the menu average.
A tiny example
In plain English: Show me each menu item’s price next to the average price of the whole menu.
SELECT item, price, AVG(price) OVER () AS avg_price
FROM menu_items;Query result
| item | price | avg_price |
|---|---|---|
| Espresso | 3.5 | 4.25 |
| Cortado | 4.25 | 4.25 |
| Cold Brew | 5 | 4.25 |
All three drinks stay as their own rows — the average (4.25) is simply repeated beside each one instead of collapsing them.
Related SQL lessons
Ready for a break?
A fresh, bite-sized SQL puzzle waits every morning. Tap tiles, see real results. No terminal, no setup, no timers.
Try today's free SQL puzzle