Window Functions vs. Aggregate Functions in SQL

SQL provides several powerful tools for analyzing and summarizing data. Two of the most important are aggregate functions and window functions.

At first glance, they can appear similar because both can calculate values such as totals, averages, minimums, and maximums. However, they behave very differently.

The key distinction is

Aggregate functions reduce multiple rows into fewer rows, while window functions perform calculations across related rows without collapsing the original result set.

Understanding this difference is essential for data analysts, data scientists, and anyone working with relational databases.

1. What Are Aggregate Functions?

An aggregate function performs a calculation on multiple rows and returns a single result for the group of rows being evaluated.

Common SQL aggregate functions include:

• SUM() — calculates a total
• AVG() — calculates an average
• COUNT() — counts rows or values
• MIN() — finds the smallest value
• MAX() — finds the largest value

For example, suppose we have a sales table:

sale_id	salesperson	region	amount
1	Alice	Nairobi	5000
2	Brian	Nairobi	7000
3	Carol	Mombasa	4000
4	David	Mombasa	6000

If we want to find the total sales for each region, we can use:

SELECT
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region;

The result would be:

region	total_sales
Nairobi	12000
Mombasa	10000

Notice that the original four rows have been reduced to two rows.

This is one of the defining characteristics of aggregate functions when used with GROUP BY.

2. What Are Window Functions?

A window function performs a calculation across a set of related rows while keeping each individual row in the result.

Window functions use the OVER() clause.

For example:

SELECT
    salesperson,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS regional_total
FROM sales;

The result would look like:

salesperson	region	amount	regional_total
Alice	Nairobi	5000	12000
Brian	Nairobi	7000	12000
Carol	Mombasa	4000	10000
David	Mombasa	6000	10000

Here, every original row remains.

The database calculates the regional total but attaches that total to each corresponding row.

3. The Main Difference

The easiest way to remember the difference is:

Aggregate functions summarize rows.

Window functions analyze rows while preserving them.

Consider the following.

Aggregate function

SELECT
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Result:

region	total_sales
Nairobi	12000
Mombasa	10000

The individual sales records are no longer visible.

Window function

SELECT
    salesperson,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS regional_total
FROM sales;

Result:

salesperson	region	amount	regional_total
Alice	Nairobi	5000	12000
Brian	Nairobi	7000	12000
Carol	Mombasa	4000	10000
David	Mombasa	6000	10000

The individual sales records are preserved.

4. Understanding GROUP BY

Aggregate functions are frequently used together with GROUP BY.

GROUP BY divides rows into groups based on one or more columns.

For example:

SELECT
    region,
    AVG(amount) AS average_sales
FROM sales
GROUP BY region;

This calculates the average sale for each region.

However, if we want to display the average alongside every individual sale, we can use a window function:

SELECT
    salesperson,
    region,
    amount,
    AVG(amount) OVER (PARTITION BY region) AS regional_average
FROM sales;

Now every sale can be compared with the average for its region.

This is particularly useful for analytical tasks.

5. Understanding PARTITION BY

PARTITION BY is one of the most important concepts in window functions.

It determines how the rows should be divided into groups, or partitions, for the calculation.

For example:

SUM(amount) OVER (PARTITION BY region)

means:

Calculate the sum of amount separately for each region.

It does not collapse the rows like GROUP BY does.

Consider:

SELECT
    salesperson,
    region,
    amount,
    AVG(amount) OVER (PARTITION BY region) AS regional_average
FROM sales;

Each region gets its own calculation, but the individual sales remain visible.

6. Window Functions Can Do More Than Aggregation

An important advantage of window functions is that they are not limited to SUM(), AVG(), MIN(), and MAX().

They can also perform tasks such as:

• Ranking
• Numbering rows
• Comparing current rows with previous rows
• Comparing current rows with following rows
• Calculating running totals
• Calculating moving averages

Common window functions include:

ROW_NUMBER()

Assigns a unique sequential number to rows.

SELECT
    salesperson,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_number
FROM sales;

RANK()

Ranks rows while allowing ties.

SELECT
    salesperson,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS sales_rank
FROM sales;

LAG()

Retrieves a value from a previous row.

SELECT
    salesperson,
    amount,
    LAG(amount) OVER (ORDER BY sale_id) AS previous_amount
FROM sales;

LEAD()

Retrieves a value from a subsequent row.

SELECT
    salesperson,
    amount,
    LEAD(amount) OVER (ORDER BY sale_id) AS next_amount
FROM sales;

These capabilities make window functions especially valuable for analytical SQL.

7. Running Totals

A common analytical requirement is calculating a running total.

Suppose we have:

sale_id	sale_date	amount
1	2026-01-01	1000
2	2026-01-02	2000
3	2026-01-03	1500
4	2026-01-04	3000

We can calculate a running total using:

SELECT
    sale_date,
    amount,
    SUM(amount) OVER (
        ORDER BY sale_date
    ) AS running_total
FROM sales;

Result:

sale_date	amount	running_total
2026-01-01	1000	1000
2026-01-02	2000	3000
2026-01-03	1500	4500
2026-01-04	3000	7500

An ordinary aggregate SUM() alone would not preserve this row-by-row progression.

8. Comparing Each Row With a Group Average

Window functions are also useful when we want to compare individual records with a group-level statistic.

For example:

SELECT
    salesperson,
    region,
    amount,
    AVG(amount) OVER (PARTITION BY region) AS regional_average,
    amount - AVG(amount) OVER (PARTITION BY region) AS difference_from_average
FROM sales;

This allows us to determine whether each salesperson's sale was above or below their regional average.

This type of analysis is common in business intelligence and performance reporting.

9. Aggregate Functions vs. Window Functions

Feature	Aggregate Functions	Window Functions
Main purpose	Summarize data	Analyze data while preserving rows
Reduces rows	Usually yes	No
Uses GROUP BY	Often	Not necessarily
Uses OVER()	No	Yes
Supports ranking	No	Yes
Supports running totals	Not directly	Yes
Supports previous/next row comparisons	No	Yes
Keeps individual records	Usually no	Yes
Common examples	SUM(), AVG(), COUNT()	ROW_NUMBER(), RANK(), LAG(), LEAD()

10. When Should You Use Each?

Use aggregate functions when:

You need a summary of the data.

Examples include:

• Total sales by region
• Average salary by department
• Number of customers by country
• Maximum transaction value
• Minimum product price

For example:

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Use window functions when:

You need analytical information while keeping individual records.

Examples include:

• Ranking employees
• Calculating running sales totals
• Finding the previous transaction
• Comparing an employee's salary with the department average
• Calculating percentage contributions
• Finding the top customers within each region

11. Can They Be Used Together?

Absolutely.

Aggregate and window functions can be combined to perform more advanced analysis.

For example, suppose we first calculate total sales by region and then want to determine each region's percentage of overall sales.

SELECT
    region,
    SUM(amount) AS regional_sales,
    SUM(SUM(amount)) OVER () AS total_sales
FROM sales
GROUP BY region;

The inner SUM(amount) calculates sales for each region.

The window function then calculates the total across those regional results.

We can extend this to calculate each region's percentage:

SELECT
    region,
    SUM(amount) AS regional_sales,
    100.0 * SUM(amount) /
        SUM(SUM(amount)) OVER () AS percentage_of_total
FROM sales
GROUP BY region;

This demonstrates how the two approaches can complement each other.

12. A Simple Mental Model

A useful way to remember the difference is to think about what happens to the rows.

Aggregate function

Many rows → fewer rows

Individual sales
      ↓
   GROUP BY
      ↓
Regional totals

Window function

Many rows → same number of rows + additional calculations

Individual sales
      ↓
 Window Function
      ↓
Individual sales + regional totals

This distinction makes it much easier to decide which technique to use.

13. Real-World Applications

Window functions are particularly useful in data analytics because many business questions require both individual-level information and group-level context.

For example, a company might want to know:

• Which customers are the top spenders?
• What is each customer's rank?
• How much has each customer spent cumulatively?
• How does each employee's performance compare with their department?
• What was the previous month's revenue?
• Which products experienced the largest change in sales?
• What percentage of total revenue does each product generate?

These questions can often be answered efficiently using window functions.

Aggregate functions, on the other hand, remain essential when the goal is simply to produce summarized reports and metrics.

IN a nutshell:

Aggregate functions and window functions are both fundamental SQL tools, but they solve different analytical problems.

Aggregate functions such as SUM(), AVG(), and COUNT() are primarily used to summarize data, often together with GROUP BY. They reduce multiple records into summarized results.

Window functions use the OVER() clause to perform calculations across related rows while keeping the original rows in the result. They are particularly powerful for ranking, running totals, comparisons, and time-based analysis.

The simplest rule to remember is:

If you want to summarize rows, think aggregate functions. If you want to analyze rows while keeping them visible, think window functions.

Once this distinction becomes clear, SQL becomes much more powerful for exploratory analysis, reporting, dashboards, and data science workflows.