What a Window Function Does

A window function computes a value over a set of rows related to the current row and returns one value for every input row. That is the whole point. GROUP BY collapses ten order rows into one total; SUM(...) OVER (...) leaves all ten rows in place and attaches the total to each of them. MySQL has had window functions since 8.0, and everything in this lesson applies to 8.4 LTS.

Where OVER goes

The OVER clause comes immediately after the function call and describes the window: PARTITION BY splits rows into groups, ORDER BY orders rows inside a group, and a frame clause narrows the rows the current row can see. All three parts are optional. An empty OVER () is shorthand for "the whole result set is one unordered window", which spelled out in full is OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) — with no ORDER BY, every row is a peer, so the default frame is the entire partition.

What trips people up

Window functions are permitted only in the select list and in ORDER BY. Windowing runs after FROM, WHERE, GROUP BY and HAVING, and before ORDER BY, LIMIT and SELECT DISTINCT. So you cannot put one in WHERE, and you cannot reference its column alias there either. To filter on a window result, compute it in a CTE or derived table and filter in the enclosing query.

-- Every order row survives, and carries totals computed across other rows.
SELECT
  o.id,
  o.customer_id,
  o.total_cents,
  -- Empty OVER (): one window covering every row the query returns.
  SUM(o.total_cents) OVER ()                            AS grand_total_cents,
  -- Same aggregate, restricted to this row's customer.
  SUM(o.total_cents) OVER (PARTITION BY o.customer_id)  AS customer_cents,
  ROUND(100 * o.total_cents / SUM(o.total_cents) OVER (), 2) AS pct_of_all
FROM orders AS o
WHERE o.placed_at >= '2026-01-01'   -- WHERE runs first: the window sees only these rows
ORDER BY o.customer_id, o.id;

-- These two columns are identical. The empty OVER () inherits every default:
--   no PARTITION BY -> the whole result set is a single partition
--   no ORDER BY     -> every row is a peer of every other row
--   no frame clause -> RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
SELECT
  id,
  COUNT(*) OVER ()                                    AS shorthand,
  COUNT(*) OVER (RANGE BETWEEN UNBOUNDED PRECEDING
                           AND UNBOUNDED FOLLOWING)   AS longhand
FROM orders;

-- Wrong: windowing happens after WHERE, so rn does not exist yet.
-- SELECT id, ROW_NUMBER() OVER (ORDER BY placed_at) AS rn
-- FROM orders WHERE rn <= 10;

WITH numbered AS (
  SELECT id, placed_at,
         ROW_NUMBER() OVER (ORDER BY placed_at, id) AS rn
  FROM orders
)
SELECT id, placed_at
FROM numbered
WHERE rn <= 10;   -- out here rn is an ordinary column of a derived result

-- GROUP BY: one row per customer. The order-level detail is gone.
SELECT customer_id, SUM(total_cents) AS spent_cents
FROM orders
GROUP BY customer_id;

-- OVER: every order row is still there, each labelled with its customer total.
SELECT id, customer_id, total_cents,
       SUM(total_cents) OVER (PARTITION BY customer_id) AS spent_cents
FROM orders;