SQL GROUP BY, aggregate functions and HAVING

#Why it matters

SQL GROUP BY and HAVING turn a million rows into the ten numbers a manager reads. Every KPI tile, every "by region" chart and most screening questions ("revenue per category where revenue exceeds X") are a group by. SQL appears in 88% of the data analyst postings we analysed, and grouping is what most of those jobs use it for. The syntax takes ten minutes to learn; the judgement (what to count, what the groups really mean) is the job.

#The concept

Examples use taxi.duckdb from What is SQL? Databases, tables and your first query.

#What GROUP BY does

group by payment_type sorts the rows into one bucket per distinct payment_type value, then each aggregate function computes one value per bucket. The output has one row per group.

The rule that follows: every column in select must either be in group by or be inside an aggregate. If you group by payment_type and also select VendorID, which of the many vendor values in that group should appear? There is no answer, so the database refuses:

Binder Error: column "VendorID" must appear in the GROUP BY clause or must be part of an aggregate function.
Either add it to the GROUP BY list, or use "ANY_VALUE(VendorID)" if the exact value of "VendorID" is not important.

MySQL used to allow this (before 5.7.5, or today with ONLY_FULL_GROUP_BY removed from sql_mode) and returns a value from an arbitrary row. You will meet old reports that rely on it; the worked example shows how wrong they can be.

#The aggregate functions

avg skipping NULLs is the one that changes conclusions; SQL NULL values: IS NULL, COALESCE and three-valued logic covers it in depth.

#WHERE vs HAVING

where fare_amount > 0 removes rows before they reach a group. having count(*) > 40000 removes groups after they are counted. Filter in where whenever the condition is about individual rows: it shrinks the data before the expensive grouping step, and some databases will not push a row condition from having down for you.

Aliases: PostgreSQL lets you group by a select alias but not use it in having (having n > 10000 fails with column "n" does not exist); repeat the aggregate instead. DuckDB, MySQL and BigQuery accept the alias in more places. Repeating the expression works everywhere.

#Grouping by expressions and positions

You can group by any expression: group by hour(tpep_pickup_datetime), group by cast(tpep_pickup_datetime as date). You can also group by position, group by 1, 2, meaning the first and second select columns. Positions are quick when exploring. Many teams ban them in saved code, because inserting a column in select silently changes the grouping. DuckDB, Snowflake and Databricks also offer group by all, which groups by every non-aggregated select column.

#Average of averages

An average of group averages is not the overall average unless every group has the same size. A zone with 3 trips and a zone with 74,000 trips count equally in the average of their averages. Always aggregate from the raw rows (or weight by the count).

#Group by the id, show the label

Labels are not always unique. In the TLC zone lookup, Corona is the name of zones 56 and 57, and three zone ids share the name Governor's Island/Ellis Island/Liberty Island. Grouping by the name merges them. Group by the id (and add the name to the group by so you can display it).

#Groups record how data was collected

Some groups describe the measuring process, not behaviour. Cash tips do not go through the meter, so the taxi data records almost every cash tip as 0. "Cash riders do not tip" is a wrong conclusion; "cash tips are not recorded" is right.

#Worked example: a month of taxi trips, summarised

#Count three ways

select count(*)                       as all_rows,
       count(passenger_count)         as with_count,
       count(distinct passenger_count) as distinct_counts,
       count(distinct PULocationID)   as zones_used
from trips;

┌──────────┬────────────┬─────────────────┬────────────┐
│ all_rows │ with_count │ distinct_counts │ zones_used │
│  int64   │   int64    │      int64      │   int64    │
├──────────┼────────────┼─────────────────┼────────────┤
│  1369765 │    1271413 │               9 │        258 │
└──────────┴────────────┴─────────────────┴────────────┘

#Payment mix

select payment_type,
       count(*)                     as trips,
       round(avg(tip_amount), 2)    as avg_tip,
       round(sum(total_amount), 0)  as revenue,
       min(fare_amount)             as min_fare,
       max(fare_amount)             as max_fare
from trips
group by payment_type
order by trips desc;

┌──────────────┬────────┬─────────┬────────────┬──────────┬──────────┐
│ payment_type │ trips  │ avg_tip │  revenue   │ min_fare │ max_fare │
│    int64     │ int64  │ double  │   double   │  double  │  double  │
├──────────────┼────────┼─────────┼────────────┼──────────┼──────────┤
│            1 │ 934473 │    2.64 │ 16190608.0 │    -21.0 │   6960.5 │
│            2 │ 322889 │     0.0 │  4642943.0 │   -250.5 │    490.0 │
│         NULL │  98352 │    1.64 │  3051486.0 │    -61.3 │    150.2 │
│            3 │   8384 │   -0.03 │    67660.0 │   -225.0 │    200.0 │
│            4 │   5667 │    0.03 │   -16890.0 │   -490.0 │    688.5 │
└──────────────┴────────┴─────────┴────────────┴──────────┴──────────┘

Read each row as a fact about the data:

• Cash (2) has an average tip of 0.0. Check how many cash trips record any tip at all:

select payment_type, count(*) as trips, count(*) filter (where tip_amount > 0) as tipped
from trips where payment_type in (1, 2) group by 1;

┌──────────────┬────────┬────────┐
│ payment_type │ trips  │ tipped │
│    int64     │ int64  │ int64  │
├──────────────┼────────┼────────┤
│            1 │ 934473 │ 890185 │
│            2 │ 322889 │     33 │
└──────────────┴────────┴────────┘

33 out of 322,889. That is a recording gap, not behaviour. Any "average tip" must be computed on card trips only and labelled that way.

• Disputes (4) have negative total revenue: refunds. A revenue total that includes them is net revenue.

• The NULL group (trips with no recorded payment type) is the third-largest group and brought in 3.05 million. Dropping NULLs would drop 13% of revenue.

#Busiest pickup zones with HAVING

Which pickup zones had more than 40,000 trips? The zone names live in the zones table, so this query looks them up with a join (the next module covers joins; read it as "attach the name"):

select t.PULocationID, z.Zone, count(*) as trips
from trips t
join zones z on z.LocationID = t.PULocationID
group by t.PULocationID, z.Zone
having count(*) > 40000
order by trips desc;

┌──────────────┬──────────────────────────────┬───────┐
│ PULocationID │             Zone             │ trips │
│    int64     │           varchar            │ int64 │
├──────────────┼──────────────────────────────┼───────┤
│          236 │ Upper East Side North        │ 74397 │
│          237 │ Upper East Side South        │ 73029 │
│          141 │ Lenox Hill West              │ 46435 │
│          239 │ Upper West Side South        │ 45486 │
│          186 │ Penn Station/Madison Sq West │ 44181 │
│          161 │ Midtown Center               │ 43922 │
│          170 │ Murray Hill                  │ 43216 │
│          142 │ Lincoln Square East          │ 42115 │
│          140 │ Lenox Hill East              │ 41621 │
│          162 │ Midtown East                 │ 40696 │
│          238 │ Upper West Side North        │ 40641 │
└──────────────┴──────────────────────────────┴───────┘

All eleven are in Manhattan, mostly residential Upper East and West Side zones in January 2021, a pandemic winter when offices were largely empty.

#Why group by the id

select z.Zone, count(*) as trips
from trips t join zones z on z.LocationID = t.PULocationID
where z.Zone = 'Corona' group by z.Zone;

select z.LocationID, z.Zone, count(*) as trips
from trips t join zones z on z.LocationID = t.PULocationID
where z.Zone = 'Corona' group by z.LocationID, z.Zone order by 1;

┌─────────┬───────┐
│  Zone   │ trips │
│ varchar │ int64 │
├─────────┼───────┤
│ Corona  │   270 │
└─────────┴───────┘
┌────────────┬─────────┬───────┐
│ LocationID │  Zone   │ trips │
│   int64    │ varchar │ int64 │
├────────────┼─────────┼───────┤
│         56 │ Corona  │   237 │
│         57 │ Corona  │    33 │
└────────────┴─────────┴───────┘

Grouping by name merged two different zones into one row of 270.

#Days with more than 50,000 trips

select cast(tpep_pickup_datetime as date) as day, count(*) as trips
from trips
where tpep_pickup_datetime >= '2021-01-01' and tpep_pickup_datetime < '2021-02-01'
group by day
having count(*) > 50000
order by day;

┌────────────┬───────┐
│    day     │ trips │
│    date    │ int64 │
├────────────┼───────┤
│ 2021-01-07 │ 50290 │
│ 2021-01-08 │ 50477 │
│ 2021-01-12 │ 50133 │
│ 2021-01-13 │ 51306 │
│ 2021-01-14 │ 53051 │
│ 2021-01-15 │ 53024 │
│ 2021-01-19 │ 51149 │
│ 2021-01-21 │ 53280 │
│ 2021-01-22 │ 54285 │
│ 2021-01-27 │ 52655 │
│ 2021-01-28 │ 56444 │
│ 2021-01-29 │ 54602 │
└────────────┴───────┘

The where keeps only January rows before grouping (the 24 out-of-range trips would otherwise create stray days). The having keeps busy days after counting. Every busy day is a weekday from Tuesday to Friday.

#Card share per vendor

select VendorID, count(*) as trips,
       round(100.0 * count(*) filter (where payment_type = 1) / count(*), 1) as card_pct
from trips group by VendorID order by VendorID;

┌──────────┬────────┬──────────┐
│ VendorID │ trips  │ card_pct │
│  int64   │ int64  │  double  │
├──────────┼────────┼──────────┤
│        1 │ 410762 │     73.3 │
│        2 │ 860651 │     73.6 │
│     NULL │  98352 │      0.0 │
└──────────┴────────┴──────────┘

Both vendors are at about 73%. The NULL vendor shows 0% because those rows have no payment type at all, not because nobody paid by card.

#The average-of-averages trap

select round(avg(fare_amount), 2) as overall_avg_fare from trips;

select round(avg(zone_avg), 2) as avg_of_zone_avgs
from (select PULocationID, avg(fare_amount) as zone_avg from trips group by PULocationID);

select round(sum(zone_avg * n) / sum(n), 2) as weighted
from (select PULocationID, avg(fare_amount) as zone_avg, count(*) as n
      from trips group by PULocationID);

┌──────────────────┐
│ overall_avg_fare │
│      double      │
├──────────────────┤
│             12.1 │
└──────────────────┘
┌──────────────────┐
│ avg_of_zone_avgs │
│      double      │
├──────────────────┤
│            27.64 │
└──────────────────┘
┌──────────┐
│ weighted │
│  double  │
├──────────┤
│     12.1 │
└──────────┘

Averaging the 258 zone averages gives 27.64, more than double the true 12.10, because the many quiet outer zones (with long, expensive trips) count as much as the busy Manhattan zones. Weighting by the trip count recovers the right answer. This bug hides in dashboards that average a pre-aggregated table.

#What MySQL used to do

-- MySQL 8.4: a three-row table (Queens, JFK Airport, 100), (Queens, Astoria, 50), (Bronx, Mott Haven, 20)
select borough, zone, sum(trips) from zone_trips group by borough;

ERROR 1055 (42000) at line 3: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'demo.zone_trips.zone' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

With set session sql_mode = '' (as on old servers):

borough	zone	sum(trips)
Queens	JFK Airport	150
Bronx	Mott Haven	20

The row says JFK Airport had 150 trips. It had 100; the 150 is all of Queens, and JFK Airport is just whichever zone MySQL read first.

#Common mistakes

• Selecting a column that is neither grouped nor aggregated. Modern databases reject it; old MySQL returns a random value. Group by it or aggregate it (any_value, min, max).

• count(col) when you meant count(*). They differ by the number of NULLs: 98,352 here.

• Row conditions in having. having payment_type = 1 works but filters late. Put row conditions in where.

• Averaging averages. Recompute from raw rows or weight by counts.

• Grouping by a label. Names repeat (Corona). Group by the key.

• Reading a data-collection gap as behaviour. Cash tips of 0 mean "not recorded".

• Positional group by 1, 2 in shared code. Fine at the prompt, fragile in a saved report.

#Interview angle

select department_id, avg(salary) as avg_salary
from employees
group by department_id
having avg(salary) > (select avg(salary) from employees);

#Practice task

On taxi.duckdb:

1. Trips and average fare by pickup hour, for January only, sorted by hour.

2. Pickup boroughs (join zones as in the busy-zones query) with more than 10,000 trips, showing trips, total revenue and average distance.

3. For card trips only, average tip percentage of fare per passenger count, excluding NULL and 0 passengers. Which group tips most?

4. Compute average trip distance per vendor as a plain avg, and again as an average of daily averages. Explain the difference in one sentence.

-- 1
select hour(tpep_pickup_datetime) as hr, count(*) as trips, round(avg(fare_amount), 2) as avg_fare
from trips
where tpep_pickup_datetime >= '2021-01-01' and tpep_pickup_datetime < '2021-02-01'
group by hr order by hr;

-- 2
select z.Borough, count(*) as trips, round(sum(t.total_amount)) as revenue,
       round(avg(t.trip_distance), 2) as avg_miles
from trips t join zones z on z.LocationID = t.PULocationID
group by z.Borough
having count(*) > 10000
order by trips desc;

-- 3
select passenger_count,
       round(100 * avg(tip_amount / nullif(fare_amount, 0)), 1) as avg_tip_pct,
       count(*) as trips
from trips
where payment_type = 1 and passenger_count > 0
group by passenger_count
order by passenger_count;

-- 4
select VendorID, avg(trip_distance) as plain_avg,
       (select avg(d) from (select avg(trip_distance) as d from trips t2
                            where t2.VendorID = t.VendorID
                            group by cast(tpep_pickup_datetime as date))) as avg_of_daily
from trips t
group by VendorID;

#Further reading

• PostgreSQL: GROUP BY and HAVING: the official rules, including functional dependency on primary keys.

• PostgreSQL: aggregate functions: every aggregate and how it handles NULLs.

• MySQL: handling of GROUP BY: ONLY_FULL_GROUP_BY and any_value.

• DuckDB: GROUP BY ALL: grouping shortcuts and their behaviour.