Window functions: keep the rows, add the context

The short answer

GROUP BY combines rows into groups. A window function calculates across related rows while keeping each input row in the result.

Use a window when you need both a row's details and its position, rank, or running total within a group.

Try a small dataset

This example uses SQL supported by PostgreSQL and MySQL 8.0 or later.

WITH orders AS (
  SELECT 1 AS id, 10 AS customer_id, 25 AS total
  UNION ALL SELECT 2, 10, 40
  UNION ALL SELECT 3, 20, 15
  UNION ALL SELECT 4, 10, 10
)
SELECT
  id,
  customer_id,
  total,
  SUM(total) OVER (
    PARTITION BY customer_id
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id ORDER BY id
  ) AS customer_order_number
FROM orders
ORDER BY id;

The running totals are 25, 65, 15, and 75. Customer 20 gets a separate window, so its total does not include customer 10's orders.

Read the window definition

PARTITION BY starts a separate calculation for each customer. ORDER BY defines the sequence inside that customer's window. The ROWS frame says to include each preceding row and the current row.

The final ORDER BY controls how the result is displayed. The ordering inside OVER does not, by itself, guarantee the final output order.

Make ties explicit

If you order by a timestamp, two rows may have the same timestamp. Add a stable tie-breaker such as the primary key when a deterministic sequence matters.

ROW_NUMBER assigns a unique sequence number. RANK gives equal peers the same rank and leaves gaps afterward. DENSE_RANK gives peers the same rank without gaps.

Try the next question

Replace the running-total expression with SUM(total) OVER (PARTITION BY customer_id). Each order now carries the customer's complete total. This is useful for calculating a row's share of its group without losing the row itself.