SQL Window Functions vs Aggregate Functions: What's the Difference?

Both window functions and aggregate functions compute values across multiple rows — but they do it in fundamentally different ways. Understanding that difference is one of the most-tested SQL concepts in interviews and certification exams.

The core difference: rows in vs rows out

Aggregate functions collapse a group of rows into a single output row. Window functions compute a value for each row without collapsing the result set.

-- Aggregate: one row per department
SELECT department, AVG(salary) AS dept_avg
FROM employees
GROUP BY department;
-- Result: 3 rows (one per department)

-- Window: original rows preserved, avg added
SELECT name, department, salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
-- Result: all 15 rows, each with its dept_avg

This single distinction is the source of most exam questions. If you need the original rows plus a summary value, use a window function. If you only need the summary, use an aggregate.

Aggregates with GROUP BY

Aggregate functions (COUNT, SUM, AVG, MIN, MAX) work by grouping rows and returning one value per group.

-- Total sales per region (aggregated — detail rows gone)
SELECT region, SUM(amount) AS total_sales, COUNT(*) AS num_orders
FROM orders
GROUP BY region;

-- You cannot access order-level columns once grouped:
-- SELECT region, order_id, SUM(amount) -- Error! order_id not in GROUP BY

Exam tip: Once you add GROUP BY, you can only SELECT columns that are in the GROUP BY clause or wrapped in an aggregate function.

Window functions with OVER()

Window functions use an OVER() clause instead of GROUP BY. The PARTITION BY inside OVER() defines the group scope, but rows are not collapsed.

-- Running total of sales per region, keeping every row
SELECT
    order_date,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region ORDER BY order_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM orders;

Notice: ROW_NUMBER() and SUM() used as window functions both operate within the defined partition, but every original order row appears in the output.

When aggregates can't do what you need

There are things window functions can do that aggregates simply cannot:

-- Compare each employee's salary to their department average
-- Aggregate alone can't do this — GROUP BY loses the employee rows.

-- Wrong approach:
SELECT name, salary, AVG(salary)  -- Error: name not in GROUP BY
FROM employees GROUP BY department;

-- Correct with window function:
SELECT
    name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

Ranking: RANK() vs COUNT() aggregate

Ranking is the most natural use case for window functions — and it's not possible with aggregates alone.

-- Rank employees by salary within each department
SELECT
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

-- RANK vs DENSE_RANK on ties (both = 90000):
-- Alice  90000  rank=1  dense_rank=1
-- Bob    90000  rank=1  dense_rank=1
-- Carol  75000  rank=3  dense_rank=2  ← RANK skips 2, DENSE_RANK doesn't

LAG and LEAD: comparing adjacent rows

LAG() and LEAD() access the previous or next row — impossible with a standard aggregate.

-- Month-over-month revenue change
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

Can you use aggregates and window functions together?

Yes — and this is where they really shine together. Aggregate first, then window over the aggregate result.

-- Total sales per day, plus a running 7-day moving average
SELECT
    order_date,
    SUM(amount) AS daily_total,
    AVG(SUM(amount)) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM orders
GROUP BY order_date
ORDER BY order_date;

Here, SUM(amount) is the aggregate that collapses to one row per day. Then AVG(SUM(amount)) OVER (...) applies a window function on top of the already-aggregated result.

Quick reference: aggregate vs window

Use this to decide at a glance:

Aggregate (GROUP BY)          Window (OVER)
─────────────────────────────────────────────
One row per group             All original rows kept
Cannot access detail columns  Detail columns still available
COUNT, SUM, AVG, MIN, MAX     RANK, ROW_NUMBER, LEAD, LAG + all aggregates
Filters via HAVING            No HAVING — filter with WHERE or subquery

If your question says "for each row, compute..." or "rank within a group" or "compare to the previous row" — that's a window function. If it says "total per group" or "count per category" — that's an aggregate.

Practice this on Examifyr: The SQL practice test includes questions on both window functions and aggregates. Take it to find out if you're ready — or check the full SQL window functions guide for deeper coverage.

Think you're ready? Prove it.

Take the free SQL readiness test. Get a score from 0–100, a topic breakdown, and your exact weak areas — in under 20 minutes.

Free · No sign-up · Instant results

More from Examifyr