WINDOW FUNCTIONS VS AGGREGATE FUNCTIONS

Window Functions vs. Aggregate Functions: What's the Real Difference?
If you've spent any time writing SQL, you've probably reached for an aggregate function like SUM() or COUNT() without thinking twice. But then someone mentions window functions, and suddenly things feel confusing. Both do calculations across rows... so what actually sets them apart?
It boils down to one simple idea: aggregates collapse your rows, window functions keep them.

Aggregate Functions: The Collapsers

Aggregate functions take a bunch of rows and squeeze them down into a single value. The individual rows disappear — you're left with just the summary.
Imagine you run a small online store and want to know your total revenue per country. Easy:

If your orders table had 10,000 rows, this query returns maybe 15 rows — one per country. The detailed row-level information is gone. That's the whole point of aggregation: summarization.

Window Functions: The Calculators That Don't Destroy Anything

Window functions, on the other hand, let you perform calculations across rows while keeping every single row intact. Think of a "window" sliding over your data — the function looks through that window to compute something, but the underlying rows stay right where they are.
Here's the same scenario, but now we want each order listed alongside the country total:

Now you get all 10,000 rows back, but each one carries a little extra context. This is perfect for questions like "how does this order compare to its country's average?" — something aggregates alone simply can't answer.

A Quick Side-by-Side

Let's say you want the average order amount per customer:

Both use AVG(). The OVER (PARTITION BY ...) clause is what flips it from "collapse mode" to "annotate mode."

The Real Power: Row-by-Row Comparisons

Window functions really shine when you need to compare rows to each other. Want to rank products by sales within each category? That's RANK(). Need each day's revenue compared to the previous day? That's LAG(). Try doing that with a plain aggregate.

When to Use Which

• Use aggregates when you only care about the summary. Dashboards, reports, quick totals — aggregates are your friend.
• Use window functions when you need detail and context at the same time. Analytics, rankings, trends, running totals — this is their home turf.

Conclusion

Honestly, the two aren't rivals at all. They complement each other beautifully, and most serious analytical queries end up using both. But once you understand that one collapses and the other annotates, the confusion melts away — and window functions stop feeling like magic and start feeling like the incredibly practical tool they are.