Skip to course content
Free SQL course

SQL for Data Analysis and AI

Unit 07.00: Aggregating without collapsing rows

Unit ID: SQL-M07-U01 - Estimated active time: 14-17 minutes Objective: use a window function to add an aggregate to detail rows, and say why GROUP BY cannot.

GROUP BY destroys the detail it summarises

Module 4 established that GROUP BY changes the grain: 1,000 orders become 2 status rows. That is often what you want - and sometimes exactly what you do not.

"Show each order with its status total beside it" cannot be answered by GROUP BY, because the moment you group, the individual orders are gone.

A window keeps both

A window function keeps every row while computing across them GROUP BY collapses many rows into one summary row, losing the detail. A window function computes across a set of rows and returns a value on every row. For daily completed revenue, the running total on 1 June is 99,801 rupees, on 2 June it is 1,96,323 and on 3 June it is 2,94,066: each value is the sum of every daily figure up to and including that row. The frame grows one row at a time, and the final row must equal the completed revenue total of 26,85,905 rupees, which is the check that proves the window covered every row exactly once. GROUP BY collapses rows. A window keeps them. GROUP BY 3 rows in, 1 row out the detail is gone SUM(...) OVER (ORDER BY day) day daily running total frame 1 Jun 99,801 99,801 2 Jun 96,522 1,96,323 3 Jun 97,743 2,94,066 The frame grows one row at a time Every row keeps its own value and gains a total. The check that proves it The last row must reach 26,85,905. Without a deterministic ORDER BY inside the window, the running total is not reproducible.
GROUP BY returns one row. A window returns every row, each carrying the total.
SELECT order_id,
       status,
       order_total,
       SUM(order_total) OVER (PARTITION BY status) AS status_total
FROM orders
ORDER BY order_id
LIMIT 3;

Every order row survives, and each carries the total for its status. The row count is unchanged at 1,000:

SELECT COUNT(*) FROM (
  SELECT order_id, SUM(order_total) OVER (PARTITION BY status) AS t FROM orders
);
-- 1000

That preserved row count is the defining property. A window function computes across rows without collapsing them.

The share-of-total pattern

This is what windows are most often needed for:

SELECT order_id,
       order_total,
       ROUND(100.0 * order_total / SUM(order_total) OVER (), 4) AS pct_of_revenue
FROM orders
ORDER BY order_total DESC
LIMIT 3;

OVER () with an empty window means "all rows". Computing a percentage of the grand total normally needs two passes or a self-join; here it is one expression.

Non-example

If you do not need the detail, do not use a window:

-- Right tool
SELECT status, SUM(order_total) FROM orders GROUP BY status;

-- Wasteful: computes the total on all 1,000 rows, then discards 998 of them
SELECT DISTINCT status, SUM(order_total) OVER (PARTITION BY status) FROM orders;

Both return two rows. The first says what it means.

Practice

Write a query showing each pending order with the total value of all pending orders beside it, and confirm the row count is 12.

Check your answer 
SELECT order_id,
       order_total,
       SUM(order_total) OVER () AS pending_total,
       COUNT(*)         OVER () AS pending_orders
FROM orders
WHERE status = 'pending'
ORDER BY order_total DESC;
-- 12 rows, pending_total 15558.00, pending_orders 12

WHERE runs before the window, so OVER () covers only the 12 pending rows - the filter has already narrowed the window's world.

Takeaway

GROUP BY collapses, a window does not. When you need a total *and* the rows behind it, that is the signal for OVER.

---