In Chapter I you walked the rose_bed and picked individual roses. In Chapter IV you visited a second bed — the flowers table, with its Marigold, Jasmine, Lotus, and Tulip — and paired each bloom with a bee. Today the gardener returns to that same bed with a fuller trait card: color and height have joined id and species, and the table has been renamed to flower_bed for its expanded shape — with more flowers, too, than the four you first met. Each question you've asked so far was about individual rows — "the tallest flower," "which bee visits which flower."

But many garden questions aren't about individual flowers at all. They're about the whole basket. How many flowers came home today? What's the average height? Which color has the most blooms? These all require looking at many rows at once and reducing them to a single number, or to a number per group.

That's what this chapter is about. The gardener returns from the flower bed, walks out the back door, and tips the basket onto the sorting table — where flowers get arranged into smaller baskets by kind, and where you can ask things like "how many?" and "on average, how tall?"

Today's harvest is bigger than the four flowers you met in Chapter IV. Ten flowers came home, of four different kinds — Marigold, Jasmine, Lotus, and Tulip. Each flower has the same trait card you saw on Chapter I's rose_bed — id, species, color, height — now applied to the flower_bed.

Take a moment to scan it. Notice: pink is the most common color, with five flowers — four Lotuses and a Tulip. White and orange tie as the rarest, one flower each. The tallest flower — id 7, a purple Tulip — reaches 30 cm. And Flower 10 — a Lotus — came in without its height recorded; that empty cell is a NULL, and it will matter when we start summing and averaging. These are the kinds of facts the sorting table makes easy to extract.

Before sorting, the simplest question: how many flowers are in the basket? SQL has a function for this: COUNT(*). The * means "every row" — count them all, regardless of what's in any particular column.

Notice the result is a single row, with a single number. No bed visualization needed — just the answer.

COUNT has siblings. Where COUNT just counts the rows, the others each do something with the values in a single column:

• SUM(height) — add all the heights together
• AVG(height) — the average
• MIN(height) — the shortest
• MAX(height) — the tallest
• COUNT(height) — how many rows have a height recorded (NULLs are skipped, unlike COUNT(*))

You can ask all of them at once. Each one becomes a column in the result.

SUM, AVG, MIN, MAX over the whole bed are useful — but you usually want them per color. "What's the average height of pink flowers, compared to purple?" "How many of each color came home?"

That's where the sorting table earns its name. The gardener takes each flower out of the big basket and drops it into a smaller basket labeled with its color. By the end, you have one small basket per color, each with its own count, its own average, its own tallest and shortest.

SQL's word for "sort flowers into a basket by some trait" is GROUP BY. The clause comes right after WHERE and tells the database which column to use as the basket label.

Watch what happens when you add GROUP BY color: the result changes from "ten rows, one per flower" to "four rows, one per color." Each row in the result represents one of the small baskets on the sorting table.

Crucially, every column you SELECT must either be the thing you grouped by, or an aggregate over the group. You can't ask for species here — a basket grouped by color has flowers of various species, no single value to name for the whole basket.

The previous query already showed counts per color. Here it is again with a tiny tweak: let's arrange the result so the most-common color comes first. Aggregate functions work in ORDER BY too.

The real power of GROUP BY is asking summary questions per basket. The average height per color. The tallest flower in each color. All in one query.

WHERE filters flowers before they reach the table. HAVING filters baskets after they've been sorted. The two look similar in syntax but apply at different stages.

Two baskets — orange and white — hold just one flower each (the lone Marigold, and a single Jasmine). If you only care about colors with multiple flowers, add a HAVING clause that filters by the basket's COUNT:

An aggregate query strings together every clause you saw in Chapter I — plus the new ones from this chapter. The clauses are still strictly ordered. The new ones — GROUP BY and HAVING — slot in between WHERE and ORDER BY.

The flower_bed is still on the table. Try any aggregate query. The result table below shows one row per basket — or one row per flower, if you leave GROUP BY out.

In the garden of the Sorting Table, the day's basket is tipped out at last. The flowers are gathered into smaller baskets — one for each color — and from each smaller basket a number is taken: how many, how tall on average, what the tallest and shortest were.

That closes Summer. Beyond it lies Autumn, with three further gardens. The first is called the Basket Inside the Basket: in it, a smaller basket is gathered first, then used inside a larger one. The second is called the Directory at the Gate: there the gardener finds any flower without walking every bed. The third is called Looking at Neighbors: each flower, standing in its own row, can see how it compares to the flowers around it. For today, the baskets are sorted, the table is wiped clean, and the sun lowers.