Window Functions vs GROUP BY

Use GROUP BY when you want fewer rows than you started with, and a window function when you want the same rows with extra columns attached.

That sounds simple, but the practical consequences are bigger than they look. A GROUP BY query produces one row per group, so any column that is not the grouping key has to be aggregated. A window function keeps every row, so you can show the row's own values and the group summary side by side without one destroying the other.

Three situations where the window function is the better tool. Keeping detail rows while showing their group total — the order list with the customer's total next to each order. Comparing each row to its group — the order next to the customer's average, or the month's revenue next to the year-to-date figure. And ranking within a group — the top three orders per customer — which a GROUP BY simply cannot express.

There are also cases where GROUP BY is the right answer, and pretending otherwise makes a query slower. If you only need the summary and no detail, GROUP BY produces strictly less data, and the engine does less work. A window function that computes a total over a partition and then discards all but one row per partition is a GROUP BY written the expensive way.

One more consideration: the two can be combined. Aggregate to a grain with GROUP BY, then use a window function over that result — that is exactly how a running total over monthly revenue is built, with a CTE that groups by month and a window function that runs over the months.

The same data, two shapes of answer. One row per customer, or every order with its customer's total attached.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25),
  (4, 2,  15.00);

-- GROUP BY: one row per customer.
SELECT customer_id,
       COUNT(*) AS order_count,
       ROUND(SUM(amount), 2) AS total_spend
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

-- Window function: every order, with its customer's total attached.
SELECT id,
       customer_id,
       amount,
       COUNT(*) OVER (PARTITION BY customer_id) AS order_count,
       ROUND(SUM(amount) OVER (PARTITION BY customer_id), 2) AS customer_total
FROM orders
ORDER BY customer_id, id;

The GROUP BY query returns two rows: customer 1 with 2 orders and 460.50, customer 2 with 2 orders and 90.25. The window query returns four rows — every order — with the same two figures repeated against each of that customer's orders. Ada's 120.00 order carries order_count 2 and customer_total 460.50, and so does her 340.50 order.

The row counts are the difference: two against four. That is not a detail — it is the whole point of choosing. If a report needs the customer's total next to every order line, the window version is the only one of the two that can produce it without a self-join back to a subquery.

Note what the window version does with the ordering of the output. The window functions compute over the partition, and the ORDER BY at the end only sorts the result. The partition boundary is what determines the totals, and it is independent of the final sort — so you can sort the output any way you like without changing the numbers, which is not true of a self-join with the aggregate computed inline.