SQL OVER and PARTITION BY
Turn an aggregate into a window function that keeps every row.
Syntax
aggregate(…) OVER (PARTITION BY … ORDER BY …)
PARTITION BY sets the groups; ORDER BY inside OVER makes running calculations.
Example
Query (on the shop database used in the SQL course)
SELECT name, category, price, ROUND(AVG(price) OVER (PARTITION BY category), 2) AS category_avg FROM products LIMIT 6;
Result
| name | category | price | category_avg |
|---|---|---|---|
| Python Crash Course (book) | Books | 45.0 | 53.0 |
| Deep Learning (book) | Books | 89.0 | 53.0 |
| SQL Pocket Guide | Books | 25.0 | 53.0 |
| Wireless mouse | Electronics | 29.0 | 155.0 |
| Mechanical keyboard | Electronics | 119.0 | 155.0 |
| USB-C hub | Electronics | 49.0 | 155.0 |
6 rows
Related
Learn it step by step in the free lesson CTEs & Window Functions, with exercises that check your queries.