Windows vs GROUP BY

GROUP BY collapses, the window keeps every row

GROUP BY is a reducer. You hand it many rows, it returns one row per group. After GROUP BY region you have four rows — one per region — and there is no way to also see the individual sales, because they were folded into the aggregate. That is the whole point of grouping: collapse detail into a summary.

A window function runs the same aggregate math but emits the result alongside every original row. Nothing collapses. The OVER (...) clause is what makes a function a window function: it tells Postgres “compute this over a set of related rows — the window — but attach the answer to each row instead of folding rows together.”

-- GROUP BY: 4 rows out, detail gone
SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region;

-- Window: every sale row kept, region total attached to each
SELECT region, salesperson, sale_date, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

The first query answers “what did each region sell?” The second answers “show me each sale and its region’s total on the same line” — which is exactly what the dashboard ticket needed. Same SUM, same region grouping logic; the only structural difference is OVER (PARTITION BY region) instead of GROUP BY region.

The percent-of-total pattern, in one query

Because the window keeps every row, you can mix a detail value and an aggregate in the same SELECT and divide them — no self-join, no subquery:

SELECT region, salesperson, amount,
       ROUND(
         100.0 * amount / SUM(amount) OVER (PARTITION BY region),
         1
       ) AS pct_of_region
FROM sales
ORDER BY region, pct_of_region DESC;

amount is the row’s own value; SUM(amount) OVER (PARTITION BY region) is its region’s total computed across all rows in the same region. Divide and you get each sale’s share of its region — per row. With plain GROUP BY this requires computing the totals separately and joining them back, which is more code and a second pass over the data.

When to use which — the decision rule

Ask yourself: does the requirement mention “each row” or “per row” next to an aggregate? That single question determines the tool.

The choice is not about which is “better”; it is about the shape of the answer you need:

• Need a summary only (one row per group)? Use GROUP BY. Reports, counts per status, daily totals.
• Need detail rows and an aggregate on the same row? Use a window. Percent-of-total, rank within group, running balance, “how does this row compare to its group’s average.”

A reliable production rule: if the word “each” or “per row” appears in the requirement next to an aggregate, you want a window. “Show each order with its customer’s lifetime total” — window. “Show the total per customer” — GROUP BY. Mixing them up is the classic junior bug: reaching for GROUP BY and then discovering the detail rows you needed are gone, or trying to put a non-aggregated column in a GROUP BY query and hitting column must appear in the GROUP BY clause.