Course contentsShow
← Data Science
Lesson 1014 of 2,14526. Advanced SQLFree lesson

Introduction to Window Aggregation Functions

Understand how SUM, AVG, COUNT, MIN, and MAX work as window functions versus regular aggregates.

Introduction to Window Aggregation Functions

You'll learn how familiar aggregate functions behave differently when used with the OVER clause to create powerful analytical queries.

The Core Idea

You already know SUM(), AVG(), COUNT(), MIN(), and MAX() as regular aggregate functions that collapse many rows into one summary value. When you add the OVER clause to these same functions, they transform into window functions that calculate aggregates while preserving individual rows.

Think of it like this: regular aggregates are like getting a single total on a receipt, while window aggregates are like seeing a running total next to each item as you add them to your cart—every item stays visible.

Regular Aggregates vs Window Aggregates

Regular aggregate (collapses rows):

SELECT department, SUM(salary) as total_salary
FROM employees
GROUP BY department;

Result: One row per department showing the total.

Window aggregate (keeps all rows):

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) as dept_total
FROM employees;

Result: Every employee row remains, with the department total calculated alongside each one.

How They Work

Window aggregation functions compute values across a "window" of rows related to the current row. You specify this window using PARTITION BY (like GROUP BY but non-collapsing) and optionally ORDER BY for cumulative calculations. Each aggregate function retains its core behavior—summing, averaging, counting, finding min/max—but applies it within the window context.

Key Takeaway: Window aggregation functions (SUM, AVG, COUNT, MIN, MAX with OVER) perform the same calculations as regular aggregates but preserve all input rows, enabling side-by-side comparisons between individual values and their group statistics.