`OVER` and `PARTITION BY`: calculate without losing detail

An aggregation with GROUP BY reduces multiple rows to a summary. That works when we only need the total, but not when we want to keep each item and show the total of its order next to it.

Window functions calculate over related rows without reducing them to a single row. The window is the set of rows that participates in the calculation for the current row. The OVER clause defines it, and PARTITION BY divides it into independent groups.

The idea

A window function returns a result for each row. You can calculate a total, average, position, or cumulative using other rows as context.

PARTITION BY restarts the calculation when the group changes. If we omit it, the window can cover the entire result.

Syntax

Funcion() OVER
(
    PARTITION BY ColumnaDeGrupo
    ORDER BY ColumnaDeOrden
)

Not all functions require both parts. A total per group can use only PARTITION BY; an accumulation also needs an order.

Example: order total in each item

SELECT
    pd.IdPedido,
    pd.NumeroPartida,
    pd.IdProducto,
    pd.Cantidad * pd.PrecioUnitario AS ImportePartida,
    SUM(pd.Cantidad * pd.PrecioUnitario) OVER
    (
        PARTITION BY pd.IdPedido
    ) AS ImportePedido
FROM dbo.PedidoDetalle AS pd
ORDER BY
    pd.IdPedido,
    pd.NumeroPartida;

OrderId	ItemNumber	ItemAmount	OrderAmount
1	1	349.00	728.00
1	2	379.00	728.00
2	1	799.00	799.00

The two items from order 1 remain visible. The total of 728 is calculated over your partition and appears next to each one.

Compare a row to the average of its group

SELECT
    p.IdProducto,
    p.IdCategoria,
    p.Nombre,
    p.Precio,
    AVG(p.Precio) OVER
    (
        PARTITION BY p.IdCategoria
    ) AS PrecioPromedioCategoria
FROM dbo.Producto AS p
WHERE p.Activo = 1;

We can keep the individual price and at the same time look at the category average.

Build a rollup

The order becomes part of the calculation when each row must incorporate the previous ones:

SELECT
    m.IdProducto,
    m.FechaMovimiento,
    m.IdMovimiento,
    m.TipoMovimiento,
    m.Cantidad,
    SUM
    (
        CASE
            WHEN m.TipoMovimiento = 'E' THEN m.Cantidad
            ELSE -m.Cantidad
        END
    ) OVER
    (
        PARTITION BY m.IdProducto
        ORDER BY m.FechaMovimiento, m.IdMovimiento
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ExistenciaCalculada
FROM dbo.MovimientoInventario AS m
ORDER BY
    m.IdProducto,
    m.FechaMovimiento,
    m.IdMovimiento;

The ROWS specification defines the frame: the portion of the window included in the calculation for the current row. Here it advances one row at a time. The identifier breaks ties between movements with the same date.

What should be checked

PARTITION BY groups for calculation, but does not sort the output. If the final order matters, we still need an outer ORDER BY.

We must also distinguish the entire window from the row frame. When we add ORDER BY inside OVER, SQL Server applies a default frame that may not represent the running total we expect, especially when ties exist. It is preferable to write ROWS explicitly.