Window Functions vs. Aggregate Functions in SQL (With Examples)

If you've ever run a GROUP BY query and then wished you could still see every row — not just the summarized ones — you've bumped into the exact reason window functions exist. Let's break down how they differ from aggregate functions, with practical examples you can run yourself.

The Core Difference

Aggregate functions (SUM(), AVG(), COUNT(), MAX(), MIN()) take many rows and collapse them into one row per group. You lose the individual row detail.

Window functions do the same kind of calculation — sums, averages, ranks — but keep every row visible. Instead of collapsing the data, they calculate "over a window" of related rows and attach the result back to each row.

Think of it like this: aggregate functions summarize; window functions annotate.

Setup: Sample Data

CREATE TABLE sales (
    employee_id INT,
    department   VARCHAR(50),
    sale_amount  NUMERIC(10,2),
    sale_date    DATE
);

INSERT INTO sales (employee_id, department, sale_amount, sale_date) VALUES
(1, 'Electronics', 5000, '2026-09-01'),
(2, 'Electronics', 3000, '2026-09-01'),
(3, 'Electronics', 7000, '2026-09-02'),
(4, 'Clothing',    2000, '2026-09-01'),
(5, 'Clothing',    4500, '2026-09-02'),
(6, 'Clothing',    3200, '2026-09-03');

1. Aggregate Functions: Collapsing Rows

SELECT
    department,
    SUM(sale_amount) AS total_sales
FROM sales
GROUP BY department;

Result:

department	total_sales
Electronics	15000
Clothing	9700

Notice: 6 rows went in, 2 rows came out. The individual employee sales are gone — you only have the group totals left.

2. Window Functions: Keeping Every Row

Now do the "same" calculation, but with a window function:

SELECT
    employee_id,
    department,
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY department) AS department_total
FROM sales;

Result:

employee_id	department	sale_amount	department_total
1	Electronics	5000	15000
2	Electronics	3000	15000
3	Electronics	7000	15000
4	Clothing	2000	9700
5	Clothing	4500	9700
6	Clothing	3200	9700

All 6 rows are still there. Each row now also knows its department's total — useful for things like calculating "what % of department sales did I personally make."

The key syntax difference is OVER (...). That's what tells SQL "don't collapse — just calculate across this window of rows and attach it to each one."

3. Ranking: Something Aggregates Can't Do at All

This is where window functions really separate themselves — aggregate functions have no concept of ranking or ordering within a group. Window functions do.

SELECT
    employee_id,
    department,
    sale_amount,
    RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS sales_rank
FROM sales;

Result:

employee_id	department	sale_amount	sales_rank
3	Electronics	7000	1
1	Electronics	5000	2
2	Electronics	3000	3
5	Clothing	4500	1
6	Clothing	3200	2
4	Clothing	2000	3

There's no GROUP BY version of "who's #1 in each department" — you'd need subqueries or self-joins to fake it. RANK(), DENSE_RANK(), and ROW_NUMBER() do it natively.

4. Running Totals: Aggregates Can't Track Progress Over Rows

SELECT
    employee_id,
    department,
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (
        PARTITION BY department
        ORDER BY sale_date
    ) AS running_total
FROM sales;

Result (Electronics):

employee_id	sale_date	sale_amount	running_total
1	2026-09-01	5000	8000
2	2026-09-01	3000	8000
3	2026-09-02	7000	15000

Adding ORDER BY inside OVER() changes the window from "the whole partition" to "the partition so far" — this is how you build running totals, cumulative sums, and moving averages. Aggregate functions with GROUP BY simply can't do this; there's no "so far" concept once rows are collapsed.

Quick Reference Table

	Aggregate Function	Window Function
Row count in output	Reduced (one per group)	Unchanged (one per input row)
Needs GROUP BY?	Yes, to group by column	No — uses PARTITION BY inside OVER()
Can rank rows?	No	Yes (RANK, ROW_NUMBER, DENSE_RANK)
Can do running totals?	No	Yes
Can mix detail + summary in one row?	No	Yes
Syntax marker	GROUP BY clause	OVER (...) clause

When to Use Which

• Use aggregate functions when you genuinely want a summary — "total sales per department," "average order value per customer" — and don't need the individual rows anymore.
• Use window functions when you want to keep row-level detail and add context from a group — "how does this sale compare to the department average," "what's this employee's rank," "what's the running total as of this sale."

A good rule of thumb: if your question has the word "per row, but compared to its group" in it, reach for a window function.