HAVING

01The problem

Harbourline is planning a loyalty discount for its most active customers. The rule the sales director proposes is simple:

"Anyone who booked more than 30 shipments in 2025."

You can count shipments per customer with GROUP BY. But you can't write WHERE COUNT(*) > 30, because WHERE runs before the counting happens.

02The concept

HAVING filters groups. It's the WHERE of grouped results: after GROUP BY has built the groups and the aggregates have been worked out, HAVING keeps the groups whose totals meet a condition.

WHERE or HAVING?

A simple test: if the condition is about one row (this shipment's status, this booking's date), it goes in WHERE. If it's about a total for a group (this customer's number of shipments, this route's average charge), it goes in HAVING.

The syntax

SELECT group_column, AGGREGATE(column) AS name
FROM table_name
WHERE row_condition
GROUP BY group_column
HAVING group_condition
ORDER BY ...;

The complete order of the clauses you've learned:

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

And the order the database runs them in, which explains what each clause can see:

Your first HAVING

Which cities have ten or more customers?

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) >= 10;

Without HAVING, you'd get all eight cities. With it, only the five big ones.

Why WHERE can't do it

Try to put the count in WHERE:

SELECT city, COUNT(*) AS customers
FROM customers
WHERE COUNT(*) >= 10
GROUP BY city;

It fails. WHERE runs at step 2, before any groups exist, so there's nothing to count yet. Conditions on aggregates always go in HAVING.

HAVING with other aggregates

Any aggregate works in HAVING. Industries with at least 15 customers:

SELECT industry, COUNT(*) AS customers
FROM customers
GROUP BY industry
HAVING COUNT(*) >= 15;

Payment methods that brought in more than ₦2 billion:

SELECT method, COUNT(*) AS payments, SUM(amount) AS total
FROM payments
GROUP BY method
HAVING SUM(amount) > 2000000000
ORDER BY total DESC;

Using WHERE and HAVING together

Most real questions need both. Which customers made more than 30 bookings in 2025?

SELECT
  customer_id,
  COUNT(*) AS shipments_2025
FROM shipments
WHERE booking_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY customer_id
HAVING COUNT(*) > 30
ORDER BY shipments_2025 DESC;

• WHERE keeps only 2025 rows: a row filter.
• GROUP BY makes one group per customer.
• HAVING COUNT(*) > 30 keeps customers with more than 30 of those rows: a group filter.

24 customers qualify.

Minimum group size

An average over three shipments isn't worth much. HAVING COUNT(*) >= n keeps only groups big enough for their averages to mean something. The routes with the highest average charge, among routes with at least 50 shipments:

SELECT
  route_id,
  COUNT(*)                   AS shipments,
  ROUND(AVG(freight_charge)) AS avg_charge
FROM shipments
WHERE status <> 'Cancelled'
GROUP BY route_id
HAVING COUNT(*) >= 50
ORDER BY avg_charge DESC;

Several conditions in HAVING

AND and OR work in HAVING just as in WHERE. Customers with more than 20 shipments and an average of at least two containers per shipment:

SELECT
  customer_id,
  COUNT(*)                  AS shipments,
  ROUND(AVG(containers), 2) AS avg_containers
FROM shipments
GROUP BY customer_id
HAVING COUNT(*) > 20
   AND AVG(containers) >= 2
ORDER BY shipments DESC;

Can HAVING use an alias?

Databases differ. SQLite and MySQL accept HAVING shipments_2025 > 30; PostgreSQL and SQL Server don't, because HAVING runs before SELECT. Repeat the aggregate (HAVING COUNT(*) > 30) and your query works everywhere.

03Example

Finance wants to know which customers booked a lot in 2025, to offer them a volume discount: customers with more than 30 bookings, busiest first.

SELECT
  customer_id,
  COUNT(*)        AS shipments_2025,
  SUM(containers) AS containers_2025
FROM shipments
WHERE booking_date BETWEEN '2025-01-01' AND '2025-12-31'
  AND status <> 'Cancelled'
GROUP BY customer_id
HAVING COUNT(*) > 30
ORDER BY shipments_2025 DESC;

04Walkthrough

1. FROM shipments: every shipment.
2. WHERE ...: 2025 bookings that weren't cancelled. Row by row.
3. GROUP BY customer_id: one group per customer.
4. HAVING COUNT(*) > 30: keep customers with more than 30 of those bookings. Group by group.
5. SELECT: the customer, their count and their containers.
6. ORDER BY shipments_2025 DESC: busiest first.

Common mistakes

Summary

05Practice

06Challenge

07More practice

Optional drills on the same skills. They don't count towards the certificate, but each one is a small, realistic request from someone at Harbourline. Do as many as you need until the pattern feels automatic.

08Check your understanding