Window Functions Fundamentals

Welcome to Window Functions Fundamentals. We are beginning Lesson 1 of 4 in this course, where we will move from summary queries to reports that keep useful detail visible. A regular GROUP BY query can calculate total order value, but it can also replace many order rows with one summary row.

Today, we will build a MySQL 8.0 report that keeps one row per order. Beside each order’s order value, we will display the order value for the complete result and the average order value. By the end, we will understand how an empty OVER() clause adds summary information without changing the report’s row count.

Throughout this course, order value means quantity * product_price summed across order items, with no order_status filter applied. Canceled and refunded orders are therefore included, so this is gross ordered value rather than recognized revenue. Production reporting normally excludes cancellations and refunds before calling a figure revenue.

Before applying a window function, we need the correct input grain. The report should contain one row per order, so we first turn line items into order totals. As you may recall from the earlier CTE work, order_totals names this stage and gives it a clear contract: one row for each order_id.

WITH order_totals AS (
    SELECT
        oi.order_id,
        ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
    FROM OrderItems AS oi
    INNER JOIN Products AS p
        ON p.product_id = oi.product_id
    GROUP BY oi.order_id
)
SELECT
    o.order_id,
    o.order_date,
    ot.order_total
FROM Orders AS o
INNER JOIN order_totals AS ot
    ON ot.order_id = o.order_id
ORDER BY o.order_date, o.order_id;

The CTE calculates order value from quantity × product_price, groups line items by order, and rounds each result to two decimal places. The outer query adds the order date. At this point, the result has order-level detail: 600 rows, one for each order.

Let us compare two similar-looking calculations. A grouped aggregate creates a new result row for each group:

SELECT
    oi.order_id,
    ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
FROM OrderItems AS oi
INNER JOIN Products AS p
    ON p.product_id = oi.product_id
GROUP BY oi.order_id
ORDER BY oi.order_id;

Here, GROUP BY oi.order_id changes the row grain. Several line-item rows become one order row. This is useful for creating order_totals, but grouping does not keep the original rows in the same result.

A window aggregate is placed in the SELECT list instead:

ROUND(SUM(order_total) OVER (), 2) AS overall_order_value

The empty OVER() means that all current rows belong to one window. The calculation is evaluated after the grouped rows have been formed, so it can inspect order totals while leaving every order row visible.

The following comparison highlights how grouping changes the row count while a window aggregate preserves it.

We can now add the complete order value beside each order. The important part is SUM(ot.order_total) OVER (): SUM performs the addition, while OVER() turns the aggregate into a window calculation.

WITH order_totals AS (
    SELECT
        oi.order_id,
        ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
    FROM OrderItems AS oi
    INNER JOIN Products AS p
        ON p.product_id = oi.product_id
    GROUP BY oi.order_id
)
SELECT
    o.order_id,
    o.order_date,
    ot.order_total,
    ROUND(SUM(ot.order_total) OVER (), 2) AS overall_order_value
FROM Orders AS o
INNER JOIN order_totals AS ot
    ON ot.order_id = o.order_id
ORDER BY o.order_date, o.order_id;

Because there is no PARTITION BY, the window contains the complete order-level result. Because there is no grouping in the outer query, the result still has 600 rows. The same overall_order_value value appears beside every order, while order_total changes from row to row.

The final ORDER BY controls only the display order. It does not change which rows belong to the empty window.

The same pattern works for an average. Since ot.order_total contains one value per order, AVG(ot.order_total) OVER () calculates the average order value, not the average value of individual line items.

WITH order_totals AS (
    SELECT
        oi.order_id,
        ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
    FROM OrderItems AS oi
    INNER JOIN Products AS p
        ON p.product_id = oi.product_id
    GROUP BY oi.order_id
)
SELECT
    o.order_id,
    o.order_date,
    ot.order_total,
    ROUND(SUM(ot.order_total) OVER (), 2) AS overall_order_value,
    ROUND(AVG(ot.order_total) OVER (), 2) AS average_order_value
FROM Orders AS o
INNER JOIN order_totals AS ot
    ON ot.order_id = o.order_id
ORDER BY o.order_date, o.order_id;

Both window expressions use the same complete set of order rows. The ROUND calls format the monetary results to two decimal places. Each order keeps its own total, while overall_order_value and average_order_value repeat consistently across all 600 rows.

Choosing the correct input grain matters. If we averaged raw line items instead, orders with more line items could have more influence on the result. Aggregating to one row per order first gives the average its intended meaning.

Window functions have a strict placement rule. MySQL cannot evaluate a window function directly in WHERE, GROUP BY, or HAVING in the same query block where the function is created. Expressions such as WHERE SUM(order_total) OVER () > 0 are invalid. We compute the window value in a CTE and then filter in an outer query. This is the one place in the course where the pattern is explained in full, and you will apply it yourself in Unit 3.

WITH order_totals AS (
    SELECT
        oi.order_id,
        ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
    FROM OrderItems AS oi
    INNER JOIN Products AS p
        ON p.product_id = oi.product_id
    GROUP BY oi.order_id
),
order_report AS (
    SELECT
        o.order_id,
        o.order_date,
        ot.order_total,
        ROUND(SUM(ot.order_total) OVER (), 2) AS overall_order_value,
        ROUND(AVG(ot.order_total) OVER (), 2) AS average_order_value
    FROM Orders AS o
    INNER JOIN order_totals AS ot
        ON ot.order_id = o.order_id
)
SELECT
    order_id,
    order_date,
    order_total,
    overall_order_value,
    average_order_value
FROM order_report
WHERE overall_order_value > 0
ORDER BY order_date, order_id;

The outer query can use overall_order_value because the window calculation has already become a regular column in order_report.

We have all the pieces. The complete statement below creates order totals, keeps the order details, repeats both whole-result metrics, and sorts by date followed by the unique order_id.

WITH order_totals AS (
    SELECT
        oi.order_id,
        ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
    FROM OrderItems AS oi
    INNER JOIN Products AS p
        ON p.product_id = oi.product_id
    GROUP BY oi.order_id
)
SELECT
    o.order_id,
    o.order_date,
    ot.order_total,
    ROUND(SUM(ot.order_total) OVER (), 2) AS overall_order_value,
    ROUND(AVG(ot.order_total) OVER (), 2) AS average_order_value
FROM Orders AS o
INNER JOIN order_totals AS ot
    ON ot.order_id = o.order_id
ORDER BY o.order_date, o.order_id;

The CTE establishes one row per order. The two empty window expressions calculate values across the complete order result without collapsing it. The final ordering makes rows with the same date appear in ascending order_id order.

The exact dates and amounts come from the provided data. This shortened preview shows the MySQL client’s table style and the important repeated values:

+----------+------------+-------------+---------------------+---------------------+
| order_id | order_date | order_total | overall_order_value | average_order_value |
+----------+------------+-------------+---------------------+---------------------+
|      104 | 2021-01-04 |       76.11 |           143123.24 |              238.54 |
|      282 | 2021-01-06 |       57.78 |           143123.24 |              238.54 |
|       31 | 2021-01-07 |      407.68 |           143123.24 |              238.54 |
|       82 | 2021-01-08 |       73.80 |           143123.24 |              238.54 |
|      ... | ...        |         ... |                 ... |                 ... |
+----------+------------+-------------+---------------------+---------------------+
600 rows in set

The order_total column varies by order. The two whole-result metrics remain the same on every row, and all 600 orders remain visible.

Today we used a CTE to create one row per order, then applied SUM(...) OVER () and AVG(...) OVER () to calculate complete-result metrics without collapsing the report. The key idea is row grain: GROUP BY changes the number of rows, while an empty OVER() repeats a calculation across the current rows.

We also saw why window values must be filtered in an outer query. In the practice section, we will build this report step by step, add average order value, and finish with deterministic ordering. The upcoming exercises will give us three focused chances to turn this pattern into a reliable SQL skill.