GROUP BY & Window Functions | Relational Databases

In this chapter

We'll split one big summary into many small ones — meeting GROUP BY and HAVING, and getting a first look at window functions, which rank rows without collapsing them.

14–17 min

The Problem in Real Life

Mike has a number now — real revenue, connected all the way from Sales to Products. It's not enough. He wants to know which products are actually carrying the store, and which ones barely move at all.

Sarah remembers the exact wall she hit in the aggregate functions chapter: SUM and COUNT could only ever summarize the whole table into one row. Splitting that same summary out per product — one row per product, not one row total — needed something Act 2 deliberately left for later.

I don't want one total. I want one total per product.

Sarah

One Number for Everything vs. One Number per Group

One total isn't the same as many totals

SUM and COUNT alone can only summarize an entire table — never split it into groups.

WHERE can't filter on a group's result

COUNT(*) doesn't exist yet when WHERE runs — filtering on it needs a completely different clause.

Grouping by the wrong column changes everything

GROUP BY p.Name and GROUP BY p.ProductID group the exact same rows differently the moment two products ever share a name.

Ranking isn't the same as summarizing

Sometimes every row needs to stay, just labeled with where it stands — that's not what GROUP BY is built for.

What Do GROUP BY and HAVING Actually Do?

SELECT, WHERE, and the aggregate functions from chapter 5 can summarize an entire table into one row — none of them can split that summary into one row per group. That's a different tool entirely, and it's exactly what Mike needs to see which products actually carry the store.

Here's the complete set for this chapter — one concept at a time, each definition sitting right above the exact query that runs it:

GROUP BY collapses rows that share a value into one summary row per group, usually paired with an aggregate function.

GROUP BY — Units Sold, Per Product

SELECT ProductID, COUNT(*) AS UnitsSold
FROM Sales
GROUP BY ProductID;

One row per ProductID instead of one row for the whole table — the exact split chapter 5's aggregate functions couldn't do alone.

The Real Best-Sellers List

SELECT p.Name, COUNT(*) AS UnitsSold
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
GROUP BY p.Name
ORDER BY UnitsSold DESC;

JOIN brings in the product's real name, GROUP BY collapses each product's sales into one row, ORDER BY puts the best-seller first.

HAVING filters the groups themselves after aggregation, the same way WHERE filters individual rows before it.

HAVING — Only Products That Sold More Than Once

SELECT p.Name, COUNT(*) AS UnitsSold
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID
GROUP BY p.Name
HAVING COUNT(*) > 1
ORDER BY UnitsSold DESC;

WHERE can't do this — COUNT(*) doesn't exist until after grouping happens — which is exactly why HAVING is the clause built specifically to filter on an aggregate's result.

A window function like RANK() computes something across a set of rows without collapsing them — every row stays, a rank just gets added alongside it.

Bonus: RANK() Without Collapsing a Single Row

SELECT p.Name, s.SaleDate,
       RANK() OVER (ORDER BY p.Price DESC) AS PriceRank
FROM Sales s
JOIN Products p ON s.ProductID = p.ProductID;

Every individual sale is still its own row; RANK() just adds a column showing where that sale's product ranks by price.

Notice exactly where HAVING slots into the order this course has already covered: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. HAVING runs after grouping for the same reason WHERE can't reference a SELECT alias — by the time WHERE runs, there's no group and no COUNT(*) yet to filter on.

Mike finally has a real best-sellers list, not just a total. The next chapter is about answers that need an answer of their own first — questions inside questions.

Key Takeaway

GROUP BY collapses rows sharing a value into one row per group; HAVING filters those finished groups the way WHERE filters individual rows, but only after grouping and aggregation have already happened.

Why This Matters

"Which products sell best," "which regions underperform," "which customers are our biggest" — nearly every real business report is a GROUP BY question in disguise. It's arguably one of the single most business-relevant skills in this entire course.

GreenMart can finally see its data broken into the groups that actually matter. The next chapter is about answering a question that needs an answer to a smaller question first.