Window Functions vs Aggregate Functions in SQL: A Beginner-Friendly Guide

If you have written SQL for more than a week, you have used SUM(), AVG() or COUNT(). They are the bread and butter of reporting. Then one day someone says, "Just use a window function for that", and suddenly you are staring at a query with OVER (PARTITION BY ...) in it and wondering what you missed.

This article is for you if you are just starting with window functions, or if you already know them and want a quick refresher. We will start with the one idea that separates the two, build both kinds of query side by side, and then explore what window functions can do that aggregates never could. Every example is small enough to run yourself.

The one-sentence difference

Aggregate functions collapse many rows into one. Window functions calculate across many rows but keep every row.

That is the whole idea. Everything else in this article is a consequence of it.

See the difference

Here is the same calculation, a total of sales per product, done both ways on four rows of data.

On the left, GROUP BY merges the two Caps rows into a single row (total 40) and the two Gloves rows into another (total 25). Four rows go in and two come out.

On the right, the window function calculates the very same totals, but every original row survives and simply receives its group's total. Four rows go in and four come out.

The vocabulary for this is granularity, which means "what one row represents". After GROUP BY, a row no longer means "one sale", it means "one product", so the granularity has changed. After a window function, a row still means "one sale", so the granularity stays the same.

Let's build both queries.

Part 1: Aggregate functions (collapse the rows)

An aggregate function takes a group of rows and returns one value per group. The classics are COUNT, SUM, AVG, MIN and MAX, and they are usually paired with GROUP BY.

Here is the data from the diagram:

CREATE TABLE product_sales (
    id       INT PRIMARY KEY,
    product  VARCHAR(20),
    sales    INT
);

INSERT INTO product_sales (id, product, sales) VALUES
(1, 'Caps',   10),
(2, 'Caps',   30),
(3, 'Gloves',  5),
(4, 'Gloves', 20);

And the total sales per product:

SELECT
    product,
    SUM(sales) AS total_sales
FROM product_sales
GROUP BY product;

product	total_sales
Caps	40
Gloves	25

Four rows became two. This is exactly what we want for a summary, but it has a limitation: once the rows are collapsed, the individual sales are gone. The id and sales columns cannot appear next to the totals, because there is no single id or sales value for a group of rows.

So what if you want to see each sale next to its product's total? That is where window functions come in.

Part 2: Window functions (same calculation, rows preserved)

A window function performs a calculation over a set of related rows and then attaches the result to every row instead of collapsing them. Here is the same total, this time as a window function:

SELECT
    id,
    product,
    sales,
    SUM(sales) OVER (PARTITION BY product) AS total_sales
FROM product_sales
ORDER BY id;

id	product	sales	total_sales
1	Caps	10	40
2	Caps	30	40
3	Gloves	5	25
4	Gloves	20	25

Same SUM, same idea of grouping by product, but four rows in and four rows out. There is no GROUP BY. The OVER (PARTITION BY product) part is what tells SQL: "calculate the sum for each product, but don't collapse anything".

Why keeping the rows matters

Once the detail is still there, you can compare each row against its group. Let's switch to a table with a bit more to look at:

CREATE TABLE employees (
    id          INT PRIMARY KEY,
    name        VARCHAR(50),
    department  VARCHAR(50),
    salary      INT
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Amina',  'Sales',       5000),
(2, 'Brian',  'Sales',       4000),
(3, 'Chloe',  'Sales',       4000),
(4, 'Ivan',   'Sales',       3000),
(5, 'David',  'Engineering', 7000),
(6, 'Esther', 'Engineering', 6000),
(7, 'Farid',  'Engineering', 6500),
(8, 'Grace',  'HR',          4500),
(9, 'Henry',  'HR',          3500);

How far is each person from their department's average salary?

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department)          AS dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC, name;

name	department	salary	dept_avg_salary	diff_from_avg
David	Engineering	7000	6500.00	500.00
Farid	Engineering	6500	6500.00	0.00
Esther	Engineering	6000	6500.00	-500.00
Grace	HR	4500	4000.00	500.00
Henry	HR	3500	4000.00	-500.00
Amina	Sales	5000	4000.00	1000.00
Brian	Sales	4000	4000.00	0.00
Chloe	Sales	4000	4000.00	0.00
Ivan	Sales	3000	4000.00	-1000.00

Nine rows in, nine rows out, and every employee now carries their department's average alongside their own salary. Without window functions this would take a subquery or a self-join. Here it is one readable line.

Anatomy of a window function

Every window function follows the same shape:

function_name(expression) OVER (
    PARTITION BY column_a
    ORDER BY column_b
    ROWS BETWEEN ... AND ...
)

Part	What it does	Required?
function_name(expression)	The calculation: SUM, AVG, ROW_NUMBER, LAG, and so on	Yes
OVER (...)	Tells SQL "this is a window function" and defines the window	Yes
PARTITION BY	Splits rows into independent groups (like GROUP BY, but without collapsing)	No
ORDER BY	Orders the rows inside each partition	Depends on the function
Frame clause (ROWS BETWEEN ...)	Narrows the window to a range of rows around the current row	No

A helpful mental model: PARTITION BY decides which rows belong together, ORDER BY decides their sequence, and the frame decides how far each row can "see".

If you leave out PARTITION BY, the entire result set is treated as one big partition:

SELECT
    name,
    salary,
    AVG(salary) OVER () AS company_avg_salary
FROM employees;

Every row will show the company-wide average, which is about 4833.33 for our data.

Side-by-side comparison

	Aggregate functions	Window functions
Rows returned	One per group	Same as the input
Granularity	Changes	Stays the same
Uses GROUP BY?	Yes	No
Uses OVER()?	No	Yes
Can show detail and summary together?	No (detail is lost)	Yes
Typical use	Totals, counts, averages per group	Rankings, running totals, comparisons with neighbouring rows

One detail that trips people up: SUM, AVG, COUNT, MIN and MAX can be used both ways. With GROUP BY they are aggregate functions. With OVER() they become window functions. The presence of OVER is what changes their behaviour.

Which functions belong to which world?

So far we have only used aggregate functions such as SUM and AVG. But the two worlds do not support the same set of functions. The slide below shows what each one can use.

GROUP BY only works with the five aggregate functions. Window functions can use those same five, and they add two more families of their own: rank functions and value (analytics) functions. That is a big part of why window functions feel so much more powerful.

Here is the full family tree, including what you are allowed to put inside the parentheses:

Family	Functions	What goes inside the parentheses
Aggregate	COUNT, SUM, AVG, MIN, MAX	An expression. COUNT accepts any data type, while SUM and AVG need numbers
Rank	ROW_NUMBER, RANK, DENSE_RANK, CUME_DIST, PERCENT_RANK, NTILE	Nothing (empty parentheses). The exception is NTILE(n), which takes a number
Value (analytics)	LEAD, LAG, FIRST_VALUE, LAST_VALUE	An expression of any data type. LEAD and LAG also accept an optional offset and default value

Two small notes on the slide. First, its last value function is labelled FIRST_VALUE a second time, but it is meant to read LAST_VALUE. Second, it shows MIN and MAX as numeric only. That is the simplest way to think about them, but in most databases they also work on text and dates, for example the earliest date or the alphabetically first name.

Let's take one tour through each family, in the same top-to-bottom order as the diagram.

Part 3: The three families in action

We will use one more small table for the time-based examples:

CREATE TABLE monthly_sales (
    month    DATE,
    revenue  INT
);

INSERT INTO monthly_sales (month, revenue) VALUES
('2026-01-01', 100),
('2026-02-01', 150),
('2026-03-01', 120),
('2026-04-01', 200);

Family 1: Aggregate functions, now with a window

You already know SUM and AVG. Adding OVER (ORDER BY ...) turns them into tools that GROUP BY cannot imitate.

Running totals. A running (cumulative) total adds up values as you move down the rows:

SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_sales;

month	revenue	running_total
2026-01-01	100	100
2026-02-01	150	250
2026-03-01	120	370
2026-04-01	200	570

The ORDER BY inside OVER tells SQL to sum from the first row up to the current row.

Moving averages. The frame clause lets you limit the window to a few rows around the current one. Here is a 3-month moving average (the current month plus the two before it):

SELECT
    month,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    ) AS moving_avg_3m
FROM monthly_sales;

month	revenue	moving_avg_3m
2026-01-01	100	100.00
2026-02-01	150	125.00
2026-03-01	120	123.33
2026-04-01	200	156.67

In the first two months there are fewer than three rows available, so SQL averages whatever exists.

Family 2: Rank functions

Rank functions put rows in order. Three of them are easy to mix up, so let's rank employees by salary within each department:

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC)       AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC)       AS dense_rnk
FROM employees
ORDER BY department, salary DESC, name;

name	department	salary	row_num	rnk	dense_rnk
David	Engineering	7000	1	1	1
Farid	Engineering	6500	2	2	2
Esther	Engineering	6000	3	3	3
Grace	HR	4500	1	1	1
Henry	HR	3500	2	2	2
Amina	Sales	5000	1	1	1
Brian	Sales	4000	2	2	2
Chloe	Sales	4000	3	2	2
Ivan	Sales	3000	4	4	3

Look at the Sales team, where Brian and Chloe are tied at 4000:

• ROW_NUMBER() never repeats. It hands out 1, 2, 3, 4 and breaks ties arbitrarily (which is why we added name to make it deterministic).
• RANK() gives tied rows the same rank, then skips numbers. Brian and Chloe are both 2, and Ivan jumps to 4.
• DENSE_RANK() gives tied rows the same rank and does not skip. Brian and Chloe are both 2, and Ivan is 3.

Notice that the parentheses are empty, exactly as the diagram promised. Rank functions do not take an argument, so the ORDER BY inside OVER is what tells them what to rank by.

The rest of the rank family works the same way: NTILE(n) splits the rows into n roughly equal buckets (NTILE(4) gives you quartiles), while PERCENT_RANK() and CUME_DIST() describe a row's relative position as a fraction.

Family 3: Value functions

Value functions fetch a value from another row and bring it onto the current one.

LAG and LEAD. Want to know how this month compares with last month? Before window functions you would self-join the table to itself. Now:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)              AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month)    AS change_vs_prev
FROM monthly_sales;

month	revenue	prev_month	change_vs_prev
2026-01-01	100	NULL	NULL
2026-02-01	150	100	50
2026-03-01	120	150	-30
2026-04-01	200	120	80

LAG looks backward and LEAD looks forward. The first row has no previous month, so it returns NULL. (Remember the optional offset and default from the diagram: LAG(revenue, 1, 0) would return 0 instead of NULL.)

FIRST_VALUE and LAST_VALUE. These return the first or last value in the window. For example, who is the top earner and the lowest earner in each department, shown next to every employee?

SELECT
    name,
    department,
    salary,
    FIRST_VALUE(name) OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS top_earner,
    LAST_VALUE(name) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_earner
FROM employees
ORDER BY department, salary DESC, name;

name	department	salary	top_earner	lowest_earner
David	Engineering	7000	David	Esther
Farid	Engineering	6500	David	Esther
Esther	Engineering	6000	David	Esther
Grace	HR	4500	Grace	Henry
Henry	HR	3500	Grace	Henry
Amina	Sales	5000	Amina	Ivan
Brian	Sales	4000	Amina	Ivan
Chloe	Sales	4000	Amina	Ivan
Ivan	Sales	3000	Amina	Ivan

Why the long frame on LAST_VALUE? When a window has an ORDER BY and no explicit frame, the default frame stops at the current row. So without ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, LAST_VALUE would simply return the current row's own value, which surprises almost everyone the first time.

A common gotcha: you cannot filter on a window function directly

Say you want the highest-paid employee in each department. It is tempting to write:

-- This will NOT work
SELECT name, department, salary
FROM employees
WHERE ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) = 1;

SQL evaluates WHERE before window functions are calculated, so the database does not know the row number yet. The fix is to compute the window function first, in a CTE or subquery, and then filter:

WITH ranked AS (
    SELECT
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;

name	department	salary
David	Engineering	7000
Grace	HR	4500
Amina	Sales	5000

The rough order SQL follows is: FROM → WHERE → GROUP BY → aggregates → HAVING → window functions → SELECT → ORDER BY. Window functions can only appear in SELECT and ORDER BY.

So, which one should I use?

Ask yourself one question: "Do I still need the individual rows in my result?"

• No, I only want the summary (total sales per region, number of customers per country): use an aggregate function with GROUP BY. You are happy for the granularity to change.
• Yes, I want the detail plus a calculation across related rows (each employee vs their department average, a rank within a group, a running total, the change from the previous row): use a window function. The granularity stays the same.

They are not rivals. Aggregates summarize, and window functions add context to rows you want to keep. Most real-world analytical queries use both.

Quick recap

• Aggregate functions collapse rows into one row per group and need GROUP BY. The granularity changes.
• Window functions calculate over a "window" of rows and return a value for every row. The granularity stays the same.
• OVER() is what turns a function into a window function.
• GROUP BY only works with aggregate functions. Window functions add two more families: rank functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, ...) and value functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE).
• PARTITION BY splits the data into groups, ORDER BY sets the sequence, and the frame clause narrows how many rows each row can see.
• ROW_NUMBER, RANK and DENSE_RANK differ only in how they treat ties.
• You cannot filter on a window function in WHERE. Wrap it in a CTE or subquery first.

Keep learning

If you prefer to learn by watching, this video is a full recap of window functions: Window functions recap on YouTube. The diagrams in this article are screenshots from that video.

Try modifying the examples above: change the partition, flip the sort order, or add a frame clause. Window functions click fastest when you break a query and see what changes.

Happy querying!