lesson 13 Calculations & data quality · intermediate

Filter groups with HAVING

Filter summaries after grouping rows.

the idea

WHERE filters rows before grouping. HAVING filters groups after aggregation. Think rows → groups → group filter → output.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

Watch for: WHERE cannot filter an aggregate computed by the same query block.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Form groups first. GROUP BY customer_id builds one row per customer with an order_count. HAVING will filter these groups next.

   step-1.sql Run

   sql›

   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   ORDER BY customer_id;

2. 2

   Keep only qualifying groups. HAVING runs after aggregation. It removes groups whose aggregate fails the test, unlike WHERE which filters source rows.

   step-2.sql Run

   sql›

   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) >= 2;