Summarizing data with COUNT, SUM, and AVG
So far every query has returned rows. Aggregate functions do something different: they collapse many rows into a single summary number. How many? How much? On average? That's the math behind nearly every dashboard KPI.
The code
A weather_stations table. How many US stations are there, and what's their average temperature reading?
SELECT COUNT(*) AS num_stations,
AVG(temperature) AS avg_temp
FROM weather_stations
WHERE country = 'USA';
COUNT(*)— how many rows (the*means "count rows, not values").AVG(temperature)— the mean of a column.SUM(),MIN(),MAX()work the same way.- The whole result is one row: the table got summarized away.
In practice
Any time someone asks "how many customers / what's the average order value / what's total revenue," the answer is an aggregate query. They're also the cheapest possible questions to ask a database — one number back instead of a million rows over the wire.
Python bridge: AVG(temperature) is df['temperature'].mean(), and COUNT(*) is len(df). The difference: SQL computes the summary inside the database, before anything gets pulled into memory.
Further reading
Aggregation — Software Carpentry SQL lesson
Try it
Practice question: predict the shape and content of the result of the query above. Then swap AVG for MAX — what changes?
Show answer
SELECT COUNT(*) AS num_stations,
AVG(temperature) AS avg_temp
FROM weather_stations
WHERE country = 'USA';
Exactly one row: num_stations (the count of US weather stations) and avg_temp (their mean temperature). The whole table collapses into a single summary row.
SELECT COUNT(*) AS num_stations,
MAX(temperature) AS max_temp
FROM weather_stations
WHERE country = 'USA';
With MAX: still one row, still two columns — num_stations is unchanged, and the second column becomes the highest recorded temperature instead of the average. Same shape, different summary.