Window Functions in SQL Server: OVER and PARTITION BY

A window function calculates over a "window" of rows related to the current row, WITHOUT collapsing rows like GROUP BY does.

function() OVER ( [PARTITION BY ...] [ORDER BY ...] [ROWS/RANGE frame] )

Families:

• Aggregate : SUM, AVG, COUNT, MIN, MAX (this file)
• Ranking : ROW_NUMBER, RANK, DENSE_RANK, NTILE (file 09)
• Offset : LAG, LEAD, FIRST_VALUE, LAST_VALUE (file 10)
• Frames : running totals, moving averages (file 11)

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Part A: GROUP BY vs Window Function

GROUP BY: one row per department (employee details are lost)

SELECT DepartmentID, AVG(Salary) AS DeptAvg
FROM dbo.Employees
GROUP BY DepartmentID;

Window: every employee row stays, department average is added

SELECT EmployeeID, FirstName, DepartmentID, Salary,
       AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvg
FROM dbo.Employees
ORDER BY DepartmentID;

Part B: OVER() with empty brackets = whole result is one window

SELECT FirstName, Salary,
       SUM(Salary)   OVER () AS TotalPayroll,
       AVG(Salary)   OVER () AS CompanyAvg,
       COUNT(*)      OVER () AS TotalEmployees,
       CAST(100.0 * Salary / SUM(Salary) OVER () AS DECIMAL(5,2)) AS PctOfPayroll
FROM dbo.Employees
ORDER BY Salary DESC;

Part C: PARTITION BY (a separate window per group)

Department stats on every row

SELECT d.DepartmentName, e.FirstName, e.Salary,
       COUNT(*)      OVER (PARTITION BY e.DepartmentID) AS DeptHeadcount,
       MIN(e.Salary) OVER (PARTITION BY e.DepartmentID) AS DeptMinSalary,
       MAX(e.Salary) OVER (PARTITION BY e.DepartmentID) AS DeptMaxSalary,
       e.Salary - AVG(e.Salary) OVER (PARTITION BY e.DepartmentID) AS DiffFromDeptAvg
FROM dbo.Employees e
JOIN dbo.Departments d ON d.DepartmentID = e.DepartmentID
ORDER BY d.DepartmentName, e.Salary DESC;

Each product's share of its category revenue

WITH ProductRevenue AS
(
    SELECT p.CategoryID, p.ProductName, SUM(oi.LineTotal) AS Revenue
    FROM dbo.OrderItems oi JOIN dbo.Products p ON p.ProductID = oi.ProductID
    GROUP BY p.CategoryID, p.ProductName
)
SELECT c.CategoryName, pr.ProductName, pr.Revenue,
       SUM(pr.Revenue) OVER (PARTITION BY pr.CategoryID) AS CategoryRevenue,
       CAST(100.0 * pr.Revenue / SUM(pr.Revenue) OVER (PARTITION BY pr.CategoryID) AS DECIMAL(5,2)) AS PctOfCategory,
       CAST(100.0 * pr.Revenue / SUM(pr.Revenue) OVER ()                         AS DECIMAL(5,2)) AS PctOfTotal
FROM ProductRevenue pr
JOIN dbo.Categories c ON c.CategoryID = pr.CategoryID
ORDER BY c.CategoryName, pr.Revenue DESC;

Multiple partitions in the same query

SELECT SaleDate, Region, EmployeeID, TotalAmount,
       SUM(TotalAmount) OVER (PARTITION BY Region)              AS RegionTotal,
       SUM(TotalAmount) OVER (PARTITION BY EmployeeID)          AS EmployeeTotal,
       SUM(TotalAmount) OVER (PARTITION BY Region, EmployeeID)  AS RegionEmployeeTotal
FROM dbo.Sales
WHERE SaleDate < '2025-02-01'
ORDER BY Region, SaleDate;

Part D: ORDER BY inside OVER (makes it cumulative)

Without ORDER BY -> total for the partition

With ORDER BY -> running (cumulative) total up to the current row

SELECT SaleDate, Region, TotalAmount,
       SUM(TotalAmount) OVER (PARTITION BY Region)                   AS RegionTotal,
       SUM(TotalAmount) OVER (PARTITION BY Region ORDER BY SaleDate, SaleID
                              ROWS UNBOUNDED PRECEDING)              AS RunningTotal
FROM dbo.Sales
WHERE Region = 'South' AND SaleDate < '2025-03-01'
ORDER BY SaleDate, SaleID;

Part E: Filtering ON a Window Function

Window functions are NOT allowed in WHERE. Wrap them in a CTE / subquery.

-- This is an ERROR:  SELECT * FROM dbo.Employees WHERE Salary > AVG(Salary) OVER (PARTITION BY DepartmentID);

WITH x AS
(
    SELECT FirstName, DepartmentID, Salary,
           AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvg
    FROM dbo.Employees
)
SELECT * FROM x WHERE Salary > DeptAvg;

Part F: Logical Order of Query Processing

  FROM -> WHERE -> GROUP BY -> HAVING -> SELECT (window functions here) -> ORDER BY
So window functions see rows AFTER WHERE / GROUP BY.

Window function over a GROUP BY result: monthly revenue + % of half-year

SELECT DATENAME(MONTH, MIN(SaleDate)) AS MonthName,
       SUM(TotalAmount)               AS MonthRevenue,
       CAST(100.0 * SUM(TotalAmount) / SUM(SUM(TotalAmount)) OVER () AS DECIMAL(5,2)) AS PctOfTotal
FROM dbo.Sales
GROUP BY MONTH(SaleDate)
ORDER BY MONTH(SaleDate);

Practice Questions

1. Show each order item with the total value of its order (no GROUP BY).
2. Show each customer's orders with that customer's total order count.
3. List employees whose salary is more than 1.2x their department average.
4. Show each region's share (%) of the total 2025 sales.
5. For each sale, show the highest single sale amount of that region.