lesson 13 Calculations & data quality · intermediate

Filter groups with HAVING

Filter summaries after grouping rows.

the idea

WHERE filters input rows before grouping. HAVING filters groups after the aggregates are computed. Put a condition where its grain is: trip conditions in WHERE, group conditions in HAVING.

Dataset: Mobility

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Count trips in each payment type. Run this. GROUP BY gives one row per payment_type_id with a trip count.

   step-1.sql Run

   sql›

   SELECT payment_type_id, COUNT(*) AS trips
   FROM trips
   GROUP BY payment_type_id;

2. 2

   Drop small groups with HAVING. Run this. HAVING runs after grouping and keeps only groups with more than 10,000 trips.the pattern

   example.sql Run

   sql›

   SELECT payment_type_id, COUNT(*) AS trips
   FROM trips
   GROUP BY payment_type_id
   HAVING COUNT(*) > 10000;

common mistake

WHERE fare_amount > 20 removes cheap trips; HAVING AVG(fare_amount) > 20 removes zones whose average is low. They answer different questions.