BeginnerLesson 7 of 304 min read

Grouping Rows with GROUP BY and HAVING

Summarise each category or customer with GROUP BY, and learn the difference between filtering rows with WHERE and groups with HAVING.

In this lesson you will

  • Summarise rows per group
  • Follow the GROUP BY rule for selected columns
  • Tell WHERE from HAVING
  • Combine both in one query

About 25 minutes

One summary per group

In lesson 6 Maya got one number for the whole shop. Now she wants one for each category: how many products, the cheapest, the dearest and the stock. She wants the number of orders per customer. These are questions about groups. The clause GROUP BY splits the rows into groups that share a value, and an aggregate function then summarises each group separately.

How GROUP BY works

Think of sorting the rows into piles, one pile per category, then summarising every pile. Each pile becomes one row in the result. This leads to a firm rule: every column in the SELECT list must either be one of the grouping columns or be wrapped in an aggregate function. A plain column that is neither has no single value for the pile, so the standard rejects it, and while some databases allow it and quietly return an arbitrary value, you should not rely on that.

WHERE versus HAVING

Two filters can be used with groups, and they act at different moments. WHERE filters the individual rows before they are grouped. HAVING filters the groups after they are summarised, so it can use aggregates such as COUNT. You cannot write COUNT(*) in a WHERE clause, because the counting has not happened yet. A handy sentence to remember: WHERE picks the rows, GROUP BY makes the piles, HAVING picks the piles.

Four grouped queries

-- 1
SELECT category, COUNT(*) AS products, MIN(price) AS cheapest, MAX(price) AS dearest, SUM(stock) AS units
FROM products GROUP BY category ORDER BY category

-- 2
SELECT customer_id, COUNT(*) AS orders FROM orders
GROUP BY customer_id ORDER BY orders DESC, customer_id

-- 3
SELECT customer_id, COUNT(*) AS orders FROM orders
GROUP BY customer_id HAVING COUNT(*) >= 3

-- 4
SELECT customer_id, COUNT(*) AS shipped FROM orders WHERE status = 'shipped'
GROUP BY customer_id HAVING COUNT(*) >= 2 ORDER BY customer_id
-- 1
category | products | cheapest | dearest | units
care | 2 | 4.00 | 5.50 | 40
plant | 4 | 6.00 | 18.00 | 61
pot | 2 | 8.50 | 15.00 | 22
-- 2
customer_id | orders
1 | 3
2 | 2
3 | 2
4 | 2
5 | 1
-- 3
customer_id | orders
1 | 3
-- 4
customer_id | shipped
1 | 3
2 | 2
4 | 2

Query 1 gave one row per category. Plants: 4 products, priced from 6.00 to 18.00, with 61 units in stock. Care products: 2 products and 40 units. Pots: 2 products and 22 units. The care stock total skips the mist spray, whose stock is NULL, because SUM ignores NULLs. Query 2 counted orders per customer. Customer 1 has the most with 3, and the sort puts the customer id second, so ties come out in a fixed order. Query 3 used HAVING to keep only customers with at least 3 orders, and only customer 1 qualified. Query 4 combines both filters. It first keeps only shipped orders with WHERE, then groups by customer, then keeps customers with at least two shipped orders with HAVING: 3 customers passed, numbers 1, 2, 4. Customer 3 is missing, because their only orders are one cancelled and one pending.

Common patterns

  • Count per group. The number of orders per customer, products per category, visits per day.
  • Total per group. Sales per month, stock per category.
  • Find the big ones. HAVING with a count or total, to show only groups above a size.
  • Group by several columns. List them all, such as category and city, and each combination becomes a group.

Remember that a group exists only if at least one row belongs to it. A customer with no orders has no pile in the orders table, so they do not appear in query 2 at all. To list them with a count of zero you need a join, which is next lesson’s topic.

Common mistake: mixing up WHERE and HAVING

Putting an aggregate in WHERE, or a plain row filter in HAVING. Use WHERE for row conditions, and HAVING only for conditions on the summary.

Made-up sample data

The data is made up. GROUP BY with HAVING is standard SQL, but databases differ in whether you may use a column alias in GROUP BY or HAVING, so check your documentation if an alias is rejected.

Write a query that counts products per category, but shows only categories with more than two products. Then say which clause did the filtering and why.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does GROUP BY do?

  1. Sorts the rows
  2. Deletes duplicates
  3. Splits rows into groups that share a value so each group can be summarised
  4. Joins two tables

Each group becomes one row in the result.

When does WHERE filter, compared with HAVING?

  1. WHERE filters rows before grouping; HAVING filters groups after
  2. They are the same
  3. HAVING runs first
  4. WHERE filters groups

WHERE picks the rows, HAVING picks the piles.

Why can COUNT not be used in WHERE?

  1. COUNT is not allowed anywhere
  2. WHERE only takes text
  3. The counting has not happened yet when WHERE runs
  4. COUNT needs a join

Use HAVING for conditions on aggregates.

Why did customer 3 not appear in query 4?

  1. Their only orders were cancelled or pending, so none shipped
  2. They have no name
  3. They joined late
  4. They live in Leeds

WHERE kept only shipped orders before grouping.

Why does a customer with no orders not appear in a grouped count?

  1. GROUP BY hides zeros
  2. No rows exist for them in the orders table
  3. Their id is NULL
  4. HAVING removes them

A group exists only if a row belongs to it.

Summary

  • GROUP BY makes one summary row per group.
  • Selected columns must be grouped or aggregated.
  • WHERE filters rows; HAVING filters groups.
  • A group needs at least one row to exist.

Next lesson

Lesson 8 shows how to combine tables with joins, including customers with no orders.

Questions

Can I group by more than one column?

Yes. List them all after GROUP BY, and each combination of values forms a group.

Can I use an alias in HAVING?

It depends on the database. Some allow it and some do not. Repeating the expression always works.

Is the data real?

No. It is a tiny made-up database.

Progress is saved in this browser.