Lessons · SQL · a window keeps every row

A total beside every row

A window function computes an aggregate over a set of rows but keeps every row: each order can show its customer's total beside its own amount.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Share of total, rank within a group, difference from the average: all need the detail and the summary on the same row, which GROUP BY cannot do because it collapses the detail.

How to think about it

Ask: do I want fewer rows (GROUP BY) or the same rows with extra columns (window)? Then say the window: OVER (PARTITION BY group ORDER BY sort).

Worked example

SELECT id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS customer_total

Each order keeps its row; the customer's total appears beside it.

FROM orders;

No GROUP BY: the row count is unchanged.

SELECT id, amount, amount * 1.0 / SUM(amount) OVER () AS share FROM orders;

OVER () is the whole table: each order's share of all revenue.

Your turn

Each review beside its film's average stars.

SELECT id, stars, AVG(stars) OVER ( film_id) AS film_avg FROM reviews;

The trap

Window functions run after WHERE and GROUP BY, so you cannot filter on one directly. Put the window in a CTE, then filter in the outer query.

Practise a window keeps every row on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.