The first time in a SQL class, aggregate functions and window functions can seem confusing because both can perform calculations such as COUNT(), SUM(), and AVG().

The key difference is simple:

Aggregate functions combine rows and return a summary, while window functions perform calculations across related rows without removing the individual rows.

What Are Aggregate Functions?

Aggregate functions perform calculations on multiple rows and return a single result.
Common aggregate functions include:

COUNT()
SUM()
AVG()
MIN()
MAX()

For example, suppose we want to calculate the average salary in each industry.

SELECT
    industry,
    AVG(salary) AS average_salary
FROM job_salary_prediction_dataset
GROUP BY industry;

The GROUP BY divides the data into groups based on industry.
The AVG() function then calculates the average salary for each group.
The result might look like this:

The important thing to notice is that the original employee-level rows are no longer displayed.
Instead, we get one row for each industry.

What Is a Window Function?

A window function performs a calculation across a set of related rows while keeping the individual rows in the result.
Window functions use the OVER() clause.
The basic syntax is:

function_name() OVER (
    PARTITION BY column
    ORDER BY column
)

For example, we can calculate the average salary for each industry while still displaying each individual record.

SELECT
    job_title,
    industry,
    salary,
    AVG(salary) OVER (
        PARTITION BY industry
    ) AS industry_average
FROM job_salary_prediction_dataset;

The result could look like:

Notice something important.
The individual salary is still available, but we have also added the average salary for that employee's industry.

This is the major advantage of a window function.

Understanding the OVER() Clause

The OVER() clause tells SQL that we want to perform a calculation across a group of rows without collapsing them.
For example:

AVG(salary) OVER (
    PARTITION BY industry
)

The PARTITION BY industry means:

Calculate the average separately for each industry.

It is similar to GROUP BY, but it behaves differently.
With:

GROUP BY industry

SQL produces one result for each industry.
With:

PARTITION BY industry

inside a window function, SQL calculates the value for each industry but keeps the individual rows.

GROUP BY vs PARTITION BY

Let's compare them.

Using GROUP BY

SELECT
    industry,
    AVG(salary) AS average_salary
FROM job_salary_prediction_dataset
GROUP BY industry;

This produces:

Healthcare    145760
Technology    145864
Finance       145802

The employee-level records are grouped together.

Using a Window Function

SELECT
    job_title,
    industry,
    salary,
    AVG(salary) OVER (
        PARTITION BY industry
    ) AS average_salary
FROM job_salary_prediction_dataset;

This produces something like:

AI Engineer          Healthcare    109413    145760
Data Analyst         Healthcare    150000    145760
Software Engineer    Technology    180000    145864
Data Scientist       Technology    160000    145864

The original rows remain.

Why Are Window Functions Useful?

Window functions are particularly useful when we want to compare an individual row with a group or calculate values across rows.
For example, we can compare an employee's salary with the average salary of their industry.

SELECT
    job_title,
    industry,
    salary,
    AVG(salary) OVER (
        PARTITION BY industry
    ) AS industry_average,
    salary - AVG(salary) OVER (
        PARTITION BY industry
    ) AS difference_from_average
FROM job_salary_prediction_dataset;

Now we can see:

The window function allows us to perform the calculation while keeping the employee's original salary.

Ranking with Window Functions

Window functions are not limited to AVG().
One of their most common uses is ranking.
For example, we can rank salaries from highest to lowest within each industry.

SELECT
    job_title,
    industry,
    salary,
    RANK() OVER (
        PARTITION BY industry
        ORDER BY salary DESC
    ) AS salary_rank
FROM job_salary_prediction_dataset;

Here:

PARTITION BY industry

creates a separate ranking for each industry.
And:

ORDER BY salary DESC

puts the highest salary first.
The result could look like:

Notice that the ranking starts again when we move to another industry.

RANK(), DENSE_RANK() and ROW_NUMBER()

SQL provides several window functions for ranking.

RANK()

RANK() OVER (ORDER BY salary DESC)

If two employees have the same salary, they receive the same rank.
For example:

Salary     Rank
200000       1
190000       2
190000       2
180000       4

Notice that rank 3 is skipped.

DENSE_RANK()

DENSE_RANK() OVER (ORDER BY salary DESC)

DENSE_RANK() also gives employees with the same salary the same rank, but it does not skip the next rank.

Salary     Rank
200000       1
190000       2
190000       2
180000       3

ROW_NUMBER()

ROW_NUMBER() OVER (ORDER BY salary DESC)

ROW_NUMBER()` gives every row a unique number.

text
Salary Row Number
200000 1
190000 2
190000 3
180000 4

Even if two employees have the same salary, they receive different row numbers.

Window Functions for Running Totals

Another common use of window functions is calculating a running total. Suppose we have a table containing monthly salary payments.
We could calculate the cumulative salary using:

sql
SELECT
month,
salary,
SUM(salary) OVER (
ORDER BY month
) AS running_total
FROM salary_payments;

The result could look like:

The SUM() calculates the total, while the OVER() clause turns it into a window calculation.

Aggregate Function vs Window Function

The easiest way to remember the difference is:

Aggregate function

sql
SELECT
industry,
AVG(salary)
FROM job_salary_prediction_dataset
GROUP BY industry;

Many rows → fewer rows
It summarizes the data.

Window function

sql
SELECT
job_title,
industry,
salary,
AVG(salary) OVER (
PARTITION BY industry
) AS industry_average
FROM job_salary_prediction_dataset;

Many rows → same rows + additional calculation
It analyzes the data without removing the individual rows.

When Should You Use Each?

Use aggregate functions when you want a summary.
For example:

• What is the average salary by industry?
• How many employees are in each industry?
• What is the highest salary?
• What is the total salary?
• What is the minimum salary?

Use window functions when you want to analyze individual rows in relation to other rows.
For example:

• What is each employee's salary rank?
• How does an employee's salary compare with the industry average?
• What is the running total?
• What was the previous employee's salary?
• What is the difference between the current and previous value?
• Who is the highest-paid employee in each industry?

Conclusion

The difference between aggregate functions and window functions becomes much easier when you remember what happens to the rows.
Aggregate functions summarize data and usually reduce the number of rows.( Keyword 'Group by')
Window functions perform calculations across related rows while keeping the original rows.( Keyword 'OVER()')

Once you understand this difference, functions such as RANK(), ROW_NUMBER(), LAG(), LEAD(), SUM() OVER() and AVG() OVER() become much easier to understand and apply to real-world data.