Advanced query techniques
Window functions, OVER and PARTITION BY
Add group metrics without collapsing rows.
Understand the concept
GROUP BY collapses rows; window functions preserve detail while calculating over a related window. OVER defines partitions, ordering and, where applicable, a frame. PARTITION BY restarts the calculation for each key group.
A window ORDER BY controls calculation order, while the final ORDER BY controls output order. Filter window results in a CTE or subquery rather than directly in WHERE. State frame boundaries explicitly for cumulative calculations and FIRST/LAST_VALUE.
A window expression computes an output column without multiplying source rows like a join. A unique ordering tie-breaker helps ROW_NUMBER but may change the intended tie semantics of ranking functions.
Use it to show category averages per product or customer totals per order.
Assuming the window ORDER BY also sorts the displayed result.
One topic. Three levels of depth.
SELECT ProductName, Price, AVG(Price) OVER () AS OverallAverage FROM lab.Product;Keeps seven product rows, each with the overall average.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Add the category product count to each product row.
Show the solution and explanation
SELECT ProductName, Category, COUNT(*) OVER (PARTITION BY Category) AS CategoryCount FROM lab.Product;Each Electronics row shows 5; the detail rows are preserved.