lesson 11 Calculations & data quality · intermediate

Group into summaries

Summarize rows that share the same group.

the idea

GROUP BY changes the grain of the result from one row per input row to one row per group. Every non-aggregated column in SELECT must be a grouping column; each aggregate is computed within its group.

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

   Start with one row per trip. Run this. Before GROUP BY, every trip is its own row.

   step-1.sql Run

   sql›

   SELECT trip_id, vendor_id, fare_amount
   FROM trips
   LIMIT 10;

2. 2

   Roll up to one row per vendor. Run this. GROUP BY vendor_id counts how many trips each vendor recorded.the pattern

   example.sql Run

   sql›

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

common mistake

Grouping by pickup zone and payment type produces one row per combination, which is a finer grain than one row per zone.