SQL GROUP BY and HAVING, Explained

Last lesson, COUNT(*) gave you one number for the whole table. But "how many products do we have" is the boring question. The useful one is "how many products do we have in each category", one number per category, all in a single query. That's GROUP BY. Then once you've got those per-group numbers, HAVING lets you keep only the groups that matter. Run this and watch one row come back per category:

Four categories in, four rows out: Electronics has 3, Home has 2, Stationery has 2, Accessories has 1. SQL squashed all eight product rows down into one row per distinct category and counted how many fell into each pile. That's the entire idea of grouping, and the rest of this lesson is just learning to aim it.

GROUP BY: one row per group

A plain SELECT category FROM products gives you eight rows, one per product, with category repeating. Add GROUP BY category and SQL collapses every row that shares a category into a single group. You go from "eight individual products" to "the four buckets those products fall into."

On its own, grouping isn't worth much. You'd just get the list of distinct categories. The payoff comes when you pair the grouped column with an aggregate function that summarizes each bucket. COUNT(*) counts the rows in the group. AVG(price) averages the prices in the group. SUM, MIN, MAX all work the same way: they fold a whole group down to one value.

Read the result a row at a time. Electronics: 3 items, average price up around 83 (the Monitor at 179 drags it up). Home: 2 items, averaging about 17. Each row is a summary of one group, not a single product. The grouped column tells you which group. The aggregate tells you something about that group.

The same pattern answers a different question against the customers table, how many customers signed up from each city:

Mumbai comes back with 3, Delhi and Pune with 2 each, Bengaluru with 1. Swap the table and the column, keep the shape: group by the thing you want one-row-per, aggregate the thing you want measured.

HAVING: filter the groups

Now you've got one row per category with a count attached. What if you only want the categories that carry at least two products? You need to filter, but not the original rows, the groups. That's what HAVING is for.

Accessories had a single product (the Backpack), so it fails COUNT(*) >= 2 and drops out. Electronics, Home, and Stationery survive. HAVING runs after the grouping is done and gets to test each group against its aggregate value. You couldn't do this with WHERE, because when WHERE runs there are no groups yet and no COUNT(*) to compare against, just individual product rows.

Try the same move on cities: which cities have more than one customer?

Bengaluru has a lone customer, so it's gone. Mumbai, Delhi, and Pune stay. HAVING is your filter for "show me only the groups where the summary meets some bar": busy cities, popular categories, customers who've placed more than five orders.

WHERE vs HAVING: the distinction that trips everyone

This is the part people get wrong, so let's nail it. Both WHERE and HAVING filter. The difference is when they run and what they filter:

• WHERE filters rows, before grouping. It looks at individual rows and throws out the ones that don't qualify, before any groups exist. It can't see aggregates because there's nothing to aggregate yet.
• HAVING filters groups, after grouping. It looks at each finished group and throws out the ones whose aggregate doesn't qualify.

Here's a query that uses both, and where the order genuinely changes the answer. We want, per category, how many products cost under 100, but only for categories that end up with at least two such products:

Follow what happened. WHERE price < 100 ran first and removed the Monitor (179) before anything was grouped, so Electronics walks into the grouping stage with only the Keyboard and Mouse. Then groups formed, then HAVING COUNT(*) >= 2 checked each one. Electronics now counts 2 (not 3), Home counts 2, Stationery counts 2, all kept. Accessories had just the Backpack, fails the HAVING, and drops.

Notice Electronics shows 2 here but showed 3 back in the first query. That's WHERE doing its job before the count. If you'd tried to filter price inside HAVING instead, you couldn't. HAVING only sees per-group aggregates, not the individual price of each product. Row-level conditions go in WHERE. Group-level conditions go in HAVING. Get that split right and these queries stop being confusing.

Clause execution order

SQL reads top to bottom, but it doesn't run top to bottom. Knowing the real order is what makes the WHERE/HAVING rules click, and it explains a few "why won't this work" errors. The logical order is:

SELECT      -- 5. pick the columns / aggregates to show
FROM        -- 1. grab the table
WHERE       -- 2. filter individual rows
GROUP BY    -- 3. fold rows into groups
HAVING      -- 4. filter the groups
ORDER BY    -- 6. sort the final rows

FROM and WHERE happen first on raw rows. Then GROUP BY builds the groups. Then HAVING filters those groups. SELECT actually runs near the end, which is why an alias you defined in SELECT (like AS product_count) sometimes can't be used in WHERE or HAVING: the alias doesn't exist yet when those clauses run. And ORDER BY is dead last, sorting whatever's left, so it's the one place an alias is always safe.

Put the whole pipeline together. Filter rows, group, filter groups, then sort the survivors:

Every clause earns its keep: WHERE drops the pricey Monitor, GROUP BY makes the per-category buckets, HAVING keeps only buckets with 2+ items, and ORDER BY items DESC puts the fullest category on top. That's the standard analytical query shape you'll write over and over.

Recap and what's next

GROUP BY collapses rows that share a value into one row per group, and you pair it with an aggregate (COUNT, SUM, AVG, MIN, MAX) to summarize each group. Every selected column must be grouped or aggregated. WHERE filters individual rows before grouping. HAVING filters whole groups after, by their aggregate. And the logical order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY) is the map that tells you which filter you actually need. The official SQLite SELECT reference lays out the full clause grammar if you want the formal version.

Coming from the previous lesson on aggregate functions? Grouping is what turns those whole-table numbers into per-group ones. Next, we pull data from more than one table at a time: SQL joins, where you'll finally connect orders back to the customers and products they point at.