Comparing Window Functions and Aggregate Functions: GROUP BY vs PARTITION BY

At first glance, window functions and aggregate functions look like similar tools for analyzing and processing data. Both do calculations like sum, average, ranking, etc. But let’s break down how they’re actually different under the hood.

Aggregate Functions (GROUP BY)

Aggregate functions work like this:

• They group rows by the columns you specify.
• After grouping, each group turns into a single result row.
• Example: you want to know the total revenue for each region.

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

Key thing: GROUP BY “compresses” your data. If you use grouping, all the rows in a group disappear — you only get the aggregation result.

Window Functions (PARTITION BY)

Window functions, on the other hand:

• Keep the original data structure (no compressing or disappearing rows!).
• Can do calculations inside “windows” — logically separated groups of rows.

Example: you want to know each city’s share of sales in its region, but keep all the data.

SELECT
    region,
    city,
    sales,
    SUM(sales) OVER (PARTITION BY region) AS total_sales_by_region
FROM sales_data;

Key thing: using window functions doesn’t remove rows, it just adds new calculated values to each row.

Example: SUM() with GROUP BY vs SUM() with PARTITION BY

To really get the difference, let’s see how SUM() works in both cases. Imagine we have a sales_data table like this:

Summing with GROUP BY

We want to know total sales for each region:

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

The result will look like this:

What happened: rows got grouped by region, and each group was “compressed” into a single row with the sales sum.

Summing with PARTITION BY

Now let’s do the same thing with a window function:

SELECT
    region,
    city,
    sales,
    SUM(sales) OVER (PARTITION BY region) AS total_sales_by_region
FROM sales_data;

Result:

What happened: PARTITION BY didn’t “compress” the rows. Instead, it calculated the sum inside each window (each region is its own window).

When should you use GROUP BY and when PARTITION BY?

GROUP BY: great for final reports

GROUP BY is handy when you want to shrink your data and get summary results at the group level. For example:

• Total sales by month.
• Counting orders by product category.

Example:

SELECT category, COUNT(*) AS total_orders
FROM orders
GROUP BY category;

PARTITION BY: perfect for analysis and details

PARTITION BY is what you want when you need to keep all the data rows and also calculate something for each one. For example:

• Find out each product’s share of sales in its category.
• Row numbering inside each group.

Example of calculating sales share:

SELECT
    category,
    product,
    sales,
    ROUND(
        (sales * 100.0) / SUM(sales) OVER (PARTITION BY category),
        2
    ) AS sales_percentage
FROM sales_data;

Example: using multiple window functions

One of the cool things about window functions is you can use several calculations at once. For example:

SELECT
    region,
    city,
    sales,
    SUM(sales) OVER (PARTITION BY region) AS total_sales,
    RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank
FROM sales_data;

Result:

Why window functions are awesome compared to GROUP BY

Keeps your original data: GROUP BY “compresses” rows, but window functions let you keep the original table structure.

Multiple calculations in one query: You can use several window functions with different PARTITION BY and ORDER BY params, while keeping your data.

Flexible analysis: Window functions let you set up calculations just how you want: running totals, ranking, share calculations, and a lot more.

Example of flexibility

Let’s combine a few functions:

SELECT
    region,
    city,
    sales,
    SUM(sales) OVER (PARTITION BY region) AS total_sales,
    AVG(sales) OVER (PARTITION BY region) AS avg_sales,
    RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank
FROM sales_data;

Result:

Limitations and common mistakes

One common mistake is trying to use PARTITION BY when you actually need to “compress” your data. For example, instead of:

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

Some folks try to write:

SELECT
    region,
    SUM(sales) OVER (PARTITION BY region) AS total_sales
FROM sales_data;

But this will return all the rows, not shrinking your data (which isn’t always what you want).

Now you totally know when to use GROUP BY and when to use window functions. It’s kinda like picking between a hammer and a screwdriver: both work with nails... but in different ways.