Window Functions: Why They’re Not Just Aggregate Functions

In an earlier post we talked about summarizing data with GROUP BY and aggregate functions using calculations like COUNT(), SUM(), and AVG() to roll rows up into meaningful summaries.

That approach is intentional: GROUP BY is designed to collapse rows so the database can return one result per group.

Window functions exist for the moments when that behavior becomes a limitation.

The Big Idea Behind Window Functions

Here’s the simplest way to think about them:

• Aggregate functions summarize rows by collapsing them.
• Window functions summarize rows while keeping every row.

The calculations themselves may look familiar, but the shape of the results is completely different.

Instead of grouping rows together, window functions evaluate a “window” of related rows and attach the result to each individual row.

What Makes a Window Function Different

Window functions rely on the OVER() clause rather than GROUP BY.

AGGREGATE_FUNCTION(column_name)
OVER (PARTITION BY column_name)

AGGREGATE_FUNCTION(column_name)
OVER (PARTITION BY column_name)

• The aggregate function defines the calculation
• PARTITION BY defines which rows are related
• OVER() tells SQL to preserve the original rows

That single structural change is what allows window functions to add context without reducing the result set.

Example: Counting Without Collapsing Rows

SELECT PaintingID,
       Title,
       CountryOfOrigin,
       COUNT(1) OVER (PARTITION BY CountryOfOrigin) AS PaintingCountPerCountry
FROM Gallery.Paintings;

SELECT PaintingID,
       Title,
       CountryOfOrigin,
       COUNT(1) OVER (PARTITION BY CountryOfOrigin) AS PaintingCountPerCountry
FROM Gallery.Paintings;

Result:

Notice how the country count repeats on each row within the same country—because the query keeps every painting row and adds group context.

Why This Pattern Matters

Window functions are about context.

They let you answer questions like:

• How many records belong to the same group as this one?
• What’s the group average alongside each individual value?
• How does this row compare to others without hiding the details?

Without window functions, these patterns often require additional queries or joins. With them, the logic stays visible and contained.

Aggregates You Already Know Still Apply

Most of the aggregate functions you’re already using work as window functions:

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

The function doesn’t change. The behavior does.

Example: Artist’s earliest and latest work year using MIN() and MAX()

SELECT Title,
       Artist,
       YearCreated,
       MIN(YearCreated) OVER (PARTITION BY Artist) AS EarliestYearForArtist,
       MAX(YearCreated) OVER (PARTITION BY Artist) AS LatestYearForArtist
FROM Gallery.Paintings;

SELECT Title,
       Artist,
       YearCreated,
       MIN(YearCreated) OVER (PARTITION BY Artist) AS EarliestYearForArtist,
       MAX(YearCreated) OVER (PARTITION BY Artist) AS LatestYearForArtist
FROM Gallery.Paintings;

Result:

Each row retains its individual values while also carrying group-level insight.

Window Functions vs GROUP BY

The distinction comes down to intent.

Use GROUP BY when your goal is summarized output with fewer rows.

Use window functions when you want to keep row-level detail and layer additional meaning on top of it.

If you ever find yourself thinking, “I need this total, but I don’t want to lose the row,” window functions are the right tool.

Key Takeaways

• Window functions calculate across related rows without collapsing results.
• OVER() is the defining feature that separates window functions from aggregates.
• PARTITION BY establishes grouping logic inside the window.
• Many aggregate functions work both with GROUP BY and as window functions.
• Window functions are about adding context, not replacing aggregation.

In the next post, we’ll build on this foundation with ranking and ordering window functions like ROW_NUMBER(), RANK(), and DENSE_RANK() — the tools that answer “where does this row fit among its peers?”