Group By

By the end of this lesson you'll be able to turn a flat table into a summary report — counting, summing, and averaging per group with GROUP BY , then filtering those groups with HAVING . This is the leap from "show me the rows" to "show me the totals", and it's the heart of every dashboard and analytics query.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Every query in this lesson runs against this little products table — the same one from the SELECT lesson. Notice the category column: Electronics appears three times , and Kitchen, Stationery, and Home appear once each. That's what makes the grouped results easy to check by hand.

In the last lesson, an aggregate like COUNT(*) squashed the entire table into a single number. GROUP BY changes that: it first splits the rows into groups (one per distinct value), then runs the aggregate once inside each group . You get one output row per group instead of one for the whole table.

Picture a pile of receipts. Counting the whole pile gives you one number. GROUP BY store is sorting them into a folder per store first, then counting each folder . Same counting, but now you can compare stores.

Add GROUP BY category and SQL builds one bucket per category, then counts the rows in each. Electronics holds ids 1, 3 and 6, so its count is 3; the other three categories have a single product each. The result has exactly one row per distinct category.

Fill in the two blanks to count how many products sit in each category. One blank is the aggregate, the other is the clause that makes the buckets. The expected result is in the comments so you can check yourself.

Any aggregate works with GROUP BY , not just COUNT . Swap in SUM(stock) to total the stock in each category, or AVG(price) to average the prices. For single-product categories the "average" and "sum" are just that one product's value — only Electronics actually combines several rows.

List several columns after GROUP BY and SQL makes a bucket for each unique combination . GROUP BY category, supplier would give one row per category-and-supplier pair. Our table only has the columns above, but the rule is the same: more grouping columns means smaller, more specific groups.

Rule of thumb: the number of output rows equals the number of distinct combinations of your GROUP BY columns.

HAVING filters the groups after they've been aggregated. It looks like WHERE , but it can test aggregate values such as COUNT(*) 1 or AVG(price) 20 . WHERE runs before grouping, so it has no idea what a group's count is yet — that's exactly the job HAVING exists to do.

One blank this time — add the keyword that filters groups so only categories with more than one product survive. Remember: it's not WHERE .

When a query has all of these, the database runs them in a fixed logical order. You write them in this order too, or you'll get a syntax error:

The query below uses three of these steps together. Walk the comments line by line to see which rows survive each stage.

Q: What's the real difference between WHERE and HAVING ?

WHERE filters rows before grouping, so it can't reference an aggregate. HAVING filters whole groups after aggregation, so it can test things like COUNT(*) or AVG(price) . They often appear in the same query.

Q: Why do I get an error when I SELECT a column that isn't in GROUP BY?

Because the group might contain many different values for that column (three product names for Electronics, say), and SQL can't pick one. Put the column in GROUP BY , or wrap it in an aggregate.

It varies by database. To stay portable, repeat the aggregate — write HAVING COUNT(*) 1 rather than HAVING product_count 1 .

Not guaranteed. Some databases happen to return groups in order, but if you need a specific order, add an explicit ORDER BY after the HAVING clause.

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm. You'll need GROUP BY , AVG , and HAVING in one query.

Practice quiz

What does GROUP BY category do?

  • Sorts rows by category
  • Removes the category column
  • Splits rows into one group per distinct category and runs aggregates per group
  • Filters out some categories

Answer: Splits rows into one group per distinct category and runs aggregates per group. GROUP BY builds one bucket per distinct value and runs the aggregate once inside each group.

How many output rows does a GROUP BY produce?

  • One per distinct group
  • One per input row
  • Always one
  • One per column

Answer: One per distinct group. GROUP BY returns one output row per group (per distinct value or combination).

What does HAVING filter?

  • Individual rows before grouping
  • Columns
  • Duplicate rows
  • Whole groups after aggregation

Answer: Whole groups after aggregation. HAVING filters whole groups after aggregation, so it can test things like COUNT(*) > 1.

Why can't WHERE filter on COUNT(*)?

  • COUNT(*) is always zero
  • WHERE runs before grouping, so the count doesn't exist yet
  • WHERE only works on text
  • COUNT(*) is not a real function

Answer: WHERE runs before grouping, so the count doesn't exist yet. WHERE filters individual rows before grouping, so it cannot reference an aggregate; use HAVING.

What is the correct logical clause order?

  • WHERE → GROUP BY → HAVING → ORDER BY
  • GROUP BY → WHERE → HAVING
  • HAVING → WHERE → GROUP BY
  • ORDER BY → WHERE → GROUP BY

Answer: WHERE → GROUP BY → HAVING → ORDER BY. The order is WHERE, then GROUP BY, then HAVING, then ORDER BY.

When grouping by category, which is a valid SELECT in standard SQL?

  • SELECT category, product_name FROM products GROUP BY category
  • SELECT product_name FROM products GROUP BY category
  • SELECT category, COUNT(*) FROM products GROUP BY category
  • SELECT price FROM products GROUP BY category

Answer: SELECT category, COUNT(*) FROM products GROUP BY category. Every selected column must be in GROUP BY or inside an aggregate; COUNT(*) is an aggregate, so this is valid.

What does GROUP BY category, supplier group by?

  • Only category
  • Each unique combination of category and supplier
  • Only supplier
  • Nothing — it errors

Answer: Each unique combination of category and supplier. Listing several columns groups by each unique combination of those columns.

Which clause should you prefer to remove rows BEFORE grouping?

  • HAVING
  • ORDER BY
  • DISTINCT
  • WHERE

Answer: WHERE. WHERE removes rows before grouping, so there is less to aggregate; HAVING is for aggregate tests.

Given the products table, what does SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 1 return?

  • All four categories
  • Only Electronics (the only category with more than one product)
  • An error
  • Zero rows

Answer: Only Electronics (the only category with more than one product). Only Electronics has more than one product, so only that group passes HAVING COUNT(*) > 1.

Does GROUP BY guarantee the result is sorted?

  • Yes, always ascending
  • Yes, by the aggregate value
  • No — add ORDER BY if you need a specific order
  • Only with HAVING

Answer: No — add ORDER BY if you need a specific order. Group order is not guaranteed; add an explicit ORDER BY when order matters.