OVER and PARTITION BY

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Calculate a per-customer count while retaining each order row.

Run the example

SELECT TOP (5) OrderID, CustomerID,
       COUNT(*) OVER (PARTITION BY CustomerID) AS CustomerOrderCount
FROM dbo.Orders
ORDER BY OrderID;

Read it step by step

1. PARTITION BY forms a window for each CustomerID.
2. COUNT(*) OVER returns a count on every original order row rather than collapsing to one row per customer.
3. The outer ORDER BY, not the partition, selects the five displayed orders.

Check the result

The count covers all orders in the customer partition before TOP selects five output rows.

Practice

Show Products 1–5 with the count of products in each CategoryID partition.

SELECT TOP (5) ProductID, CategoryID,
       COUNT(*) OVER (PARTITION BY CategoryID) AS CategoryProductCount
FROM dbo.Products
ORDER BY ProductID;