One summary per group with GROUP BY

Aggregates collapse a whole table into one number. GROUP BY goes one step further: it splits rows into buckets first, then computes one aggregate per bucket. One number per country, per day, per category — the breakdown behind every dashboard chart.

The code

Station counts per country, for stations west of 100°W:

SELECT country, COUNT(*) AS num_stations
FROM weather_stations
WHERE lon < -100.0
GROUP BY country;

The execution order matters:

1. WHERE lon < -100.0 — filter rows first.
2. GROUP BY country — bucket the survivors by country.
3. COUNT(*) — tally each bucket. One result row per country.

Every column in the SELECT must either be in the GROUP BY or wrapped in an aggregate — otherwise the database can't know which row's value you mean.

In practice

This is the single most useful query shape in analytics: revenue per month, errors per service, users per plan. If you've ever written .groupby() in pandas after pulling a whole table, this is the same operation — pushed into the database where it belongs.

Try it

Practice question: using this sample of weather_stations, predict the result of the query above. Then add AVG(elevation) AS avg_elevation to the SELECT — how does the result change?

SELECT country, COUNT(*) AS num_stations
FROM weather_stations
WHERE lon < -100.0
GROUP BY country;

SELECT country, COUNT(*) AS num_stations,
       AVG(elevation) AS avg_elevation
FROM weather_stations
WHERE lon < -100.0
GROUP BY country;

Further reading

GROUP BY in SQL: How to Aggregate and Analyze Data Efficiently