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, after the aggregates are calculated. WHERE filters rows, before grouping.

The full order of clauses is now:

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

03Example

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;

04Walkthrough

The database works through it in this order:

1. FROM shipments takes all shipments.
2. WHERE … keeps only 2025 bookings. This is a row filter.
3. GROUP BY customer_id builds one group per customer.
4. COUNT(*) counts each group.
5. HAVING COUNT(*) > 30 keeps only groups with more than 30 shipments. This is a group filter.
6. ORDER BY sorts what's left.

Use both together when you need both kinds of filter. Here, routes that are expensive on average and busy enough for the average to mean something:

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;

05Practice

06Challenge

07Check your understanding