Module 7 · SQL for Data Analysis
Aggregate functions
Summarise many rows into one answer with COUNT, SUM, AVG, MIN and MAX.
About 25 minutes
The problem
The managing director has a board meeting tomorrow and asks for a few numbers about 2025:
- How many shipments did we handle?
- How much did we charge in total?
- What was the average shipment worth?
None of these needs a list of shipments. Each needs one number calculated from many rows.
The concept
Aggregate functions take a column of values and return a single value.
| Function | Returns |
|---|---|
COUNT(*) | the number of rows |
COUNT(column) | the number of rows where that column is not NULL |
COUNT(DISTINCT column) | the number of different values |
SUM(column) | the total |
AVG(column) | the average |
MIN(column) / MAX(column) | the smallest / largest value |
Aggregates ignore NULLs. AVG of a column with some NULLs averages only the values that exist.
ROUND(value, 2) rounds to two decimal places, which keeps averages readable.
Example
SELECT
COUNT(*) AS shipments,
SUM(freight_charge) AS total_charged,
ROUND(AVG(freight_charge)) AS average_charge
FROM shipments
WHERE booking_date BETWEEN '2025-01-01' AND '2025-12-31'
AND status <> 'Cancelled';Walkthrough
WHEREkeeps 2025 bookings and removes cancelled ones, which were never charged.- The three aggregates then run over the rows that remain.
- The result is one row, however many shipments there were.
The difference between COUNT(*) and COUNT(column) matters when a column has NULLs:
SELECT
COUNT(*) AS customers,
COUNT(account_manager_id) AS with_manager,
COUNT(DISTINCT account_manager_id) AS managers_used
FROM customers;COUNT(*) counts every customer. COUNT(account_manager_id) skips the ones with no manager. COUNT(DISTINCT …) counts how many different managers look after customers.
Practice
Practice
How much did Harbourline receive in payments in total? Return one column named total_received.
Ctrl + Enter to run. Tab indents; press Esc then Tab to leave the editor.
Practice
How many shipments are in transit right now (status 'In transit')? Name the column in_transit.
Ctrl + Enter to run. Tab indents; press Esc then Tab to leave the editor.
Challenge
Challenge · optional
For 2026 bookings (from '2026-01-01'), return in one row: the number of different customers who booked, the largest number of containers in a single shipment, and the average weight_kg rounded to a whole number.
Ctrl + Enter to run. Tab indents; press Esc then Tab to leave the editor.
Check your understanding
Answer every question to check.