A window aggregate calculates values such as SUM, AVG, MIN, or MAX across related rows without collapsing the individual records.
It allows detailed transaction records to be displayed alongside customer, department, or group-level totals.
Show every order along with the customer's total sales.
SELECT
c.CustomerName,
o.OrderID,
o.OrderAmount,
SUM(o.OrderAmount) OVER (
PARTITION BY o.CustomerID
) AS CustomerTotal
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;Used in sales analytics, financial reporting, customer dashboards, and transaction analysis.
- Window aggregates preserve detail rows.
- JOIN provides related descriptive information.
- PARTITION BY defines the calculation group.
- SUM, AVG, MIN, and MAX can be used as window functions.
- Useful for detail-versus-summary comparisons.
How is a window aggregate different from GROUP BY?
GROUP BY collapses rows into groups, while a window aggregate calculates across rows without removing the individual records.
Can window functions be used after a JOIN?
Yes. Window functions can operate on the rows produced by the JOIN.