lesson 10 Calculations & data quality · intermediate

COUNT, SUM & AVG

Count rows and calculate totals and averages.

the idea

COUNT(*) counts rows; COUNT(column) counts rows where that column is known. SUM adds values and AVG divides the sum by the number of known values. Without GROUP BY, an aggregate query returns one row for the whole table.

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

   Look at trip-level numbers. Run this. Each row is one trip with fare, tip, and passenger count.

   step-1.sql Run

   sql›

   SELECT trip_id, fare_amount, tip_amount, passenger_count
   FROM trips
   LIMIT 10;

2. 2

   Collapse the table to one summary row. Run this. COUNT and AVG over all rows give totals and averages for the whole trips table.the pattern

   example.sql Run

   sql›

   SELECT
     COUNT(*) AS trips,
     ROUND(AVG(trip_distance_miles), 2) AS avg_miles
   FROM trips;

common mistake

COUNT(passenger_count) is smaller than COUNT(*) because flex-fare trips have no recorded passenger count. AVG ignores those trips rather than treating them as zero.