HAVING

Lesson 9 of 21 · Part 2 · Summarizing · about 15 minutes

In the last lesson we grouped the orders by customer and counted each customer's orders. Now we want only the customers with 2 or more orders. First, all of them:

main.sql

1SELECT customer_id, COUNT(*) AS order_count2FROM orders3GROUP BY customer_id4ORDER BY customer_id;

Output

customer_id	order_count
1	3
2	2
3	1
4	2
5	2
7	1
8	1

7 rows

We want the rows where order_count is at least 2. That is a condition on groups, and it takes HAVING.

HAVING

HAVING goes after GROUP BY, and its condition can use aggregate functions:

main.sql

1SELECT customer_id, COUNT(*) AS order_count2FROM orders3GROUP BY customer_id4HAVING COUNT(*) >= 25ORDER BY customer_id;

Output

customer_id	order_count
1	3
2	2
4	2
5	2

4 rows

Press Step and watch: GROUP BY first splits the orders into 7 groups, then HAVING looks at them one group at a time and drops every group whose COUNT(*) is below 2, whole. 4 groups are left.

HAVING can use any aggregate, even one that isn't in SELECT. The orders worth more than 40:

main.sql

1SELECT order_id2FROM order_items3GROUP BY order_id4HAVING SUM(quantity * unit_price) > 405ORDER BY order_id;

Output

order_id
3
5
7
8

4 rows

No aggregates in WHERE

Why not just write WHERE COUNT(*) >= 2? Try it:

main.sql

1SELECT customer_id, COUNT(*) AS order_count2FROM orders3WHERE COUNT(*) >= 24GROUP BY customer_id;

Output

ERROR 1111 (HY000): Invalid use of group function

ERROR 1111: an aggregate function in the wrong place. WHERE looks at the rows one by one before grouping; at that point there are no groups yet, so there is nothing for COUNT(*) to count.

WHERE vs HAVING

Both keep what matches a condition. The difference is when, and what they look at:

	When	Looks at	Aggregates allowed?
`WHERE`	before grouping	each row	no
`HAVING`	after grouping	each group	yes

You can use both. Counting only shipped orders, find the customers with 2 shipped orders:

main.sql

1SELECT customer_id, COUNT(*) AS shipped2FROM orders3WHERE status = 'shipped'4GROUP BY customer_id5HAVING COUNT(*) >= 26ORDER BY customer_id;

Output

customer_id	shipped
1	2
5	2

2 rows

Press Step again; this time it's even clearer. WHERE first drops the pending and cancelled orders row by row, and only what is left is grouped; after grouping, HAVING drops the groups with fewer than 2 orders. Customer 2 placed 2 orders, but one is still pending, so by the time HAVING sees his group it has only 1 row, and he is gone.

Guess first

The products over 10: Backpack and Tote bag (Bags), Water bottle (Kitchen), Headphones, Desk lamp and Phone case (Electronics). How many rows does the result have?

1SELECT category, COUNT(*)2FROM products3WHERE price > 104GROUP BY category5HAVING COUNT(*) >= 2;

WHERE first drops the products of 10 or less, so no Stationery is left, not even a group. Three groups remain: Bags 2, Kitchen 1, Electronics 3. HAVING drops Kitchen: 2 rows.

Use WHERE when you can

A condition that has nothing to do with aggregates can go in either WHERE or HAVING, with the same result:

main.sql

1SELECT category, COUNT(*)2FROM products3GROUP BY category4HAVING category <> 'Electronics'5ORDER BY category;

Output

category	COUNT(*)
Bags	2
Kitchen	2
Stationery	2

3 rows

But this groups and counts the 4 electronics rows too, only to throw them away at the end. WHERE category <> 'Electronics' leaves them out from the start, which is more direct. The rule is simple: **conditions on rows go in WHERE, conditions on aggregate results go in HAVING**.

Categories with a high average

The categories whose average price is over 20:

main.sql

1SELECT category, ROUND(AVG(price), 2) AS avg_price2FROM products3GROUP BY category4HAVING AVG(price) > 205ORDER BY category;

Output

category	avg_price
Bags	25.45
Electronics	26.88

2 rows

Tip: MySQL also lets HAVING use an alias from SELECT (HAVING avg_price > 20), but many other databases don't. Writing the aggregate again works everywhere.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

Exercise 1

Well-stocked categories

Get the categories whose stock adds up to more than 100: two columns, category and total_stock, sorted by category.

It should print

category	total_stock
Electronics	245
Stationery	200

2 rows

exercise1.sql

1SELECT category, SUM(stock) AS total_stock2FROM products3GROUP BY category4ORDER BY category;

Output

Press Run or Ctrl+Enter

Kitchen has exactly 100, and "more than 100" leaves it out.

Exercise 2

Regular customers

Leaving out cancelled orders, find the customers with at least 2 orders: two columns, customer_id and order_count, sorted by customer_id.

It should print

customer_id	order_count
1	3
2	2
5	2

3 rows

exercise2.sql

1SELECT customer_id, status FROM orders;

Output

Press Run or Ctrl+Enter

Exercise 3

Fix it: ERROR 1111

This should find the orders with 4 or more items in all, but it gives ERROR 1111. Fix it; the columns are order_id and items, sorted by order_id.

It should print

order_id	items
1	4
5	4
6	5
9	4
12	4

5 rows

exercise3.sql

1SELECT order_id, SUM(quantity) AS items2FROM order_items3WHERE SUM(quantity) >= 44GROUP BY order_id5ORDER BY order_id;

Output

Press Run or Ctrl+Enter

What you learned

HAVING comes after GROUP BY and filters groups; its condition can use aggregate functions.
WHERE drops rows before grouping; HAVING drops groups after grouping.
An aggregate in WHERE gives ERROR 1111.
You can use both: conditions on rows in WHERE, conditions on aggregate results in HAVING.
Only some databases allow a SELECT alias in HAVING; writing the aggregate again is the safe choice.