Windows: aggregates that keep the rows

Aggregate without grouping

GROUP BY collapses rows. A window function computes across a set of rows but keeps every row — you get the detail and the summary side by side. The OVER () clause makes any aggregate a window:

SELECT title, price,
       AVG(price) OVER () AS avg_all
FROM books;

Every row now carries the overall average alongside its own price. PARTITION BY restarts the window per group — here, the average price within each genre:

SELECT title, genre, price,
       AVG(price) OVER (PARTITION BY genre) AS genre_avg
FROM books;