COUNT, SUM and AVG are the three answers to the question "what happened in this pile of rows?" — one counts them, one adds them up, one finds the mean. They differ in one crucial way from everything in module 03: an aggregate collapses many rows into a single value, and the rules about NULL and integer division make that collapse quietly lossy if you do not watch them.
What do COUNT, SUM and AVG summarize?
The cycle-hire company in this module keeps one row per rental. A single row tells you a bike went out for three days at thirty-two pounds a day; it does not tell you whether the business is healthy. The aggregate functions read the whole table (or the whole group) and hand back one number per bucket.
CREATE TABLE rentals (
rental_id INTEGER PRIMARY KEY,
customer TEXT,
city TEXT,
bike_type TEXT,
rental_days INTEGER,
daily_rate REAL,
discount_pct INTEGER,
status TEXT,
returned_on TEXT
);
That is the whole model for this module: one flat fact table, and every question answered by collapsing it differently. Twenty-four rows load it: three cities, five kinds of bike, and a mix of completed, cancelled and still-out hires. Two rows carry no discount, five carry no return date, and six customers came back for a second hire — those gaps are deliberate, because every aggregate in this module has to cope with them.
INSERT INTO rentals (rental_id, customer, city, bike_type, rental_days, daily_rate, discount_pct, status, returned_on) VALUES
(1, 'Dana Whitcombe', 'Bristol', 'cargo', 5, 23.10, 20, 'completed', '2025-05-02'),
(2, 'Aisha Rahman', 'Bristol', 'cargo', 4, 38.50, NULL, 'completed', '2025-04-11'),
(3, 'Callum Reid', 'Bristol', 'e-bike', 4, 45.00, 0, 'completed', '2025-04-18'),
(4, 'Eoin Brennan', 'Bristol', 'e-bike', 10, 45.00, 10, 'active', NULL),
(5, 'Aisha Rahman', 'Bristol', 'hybrid', 5, 27.80, 5, 'completed', '2025-04-25'),
(6, 'Fatima Zahra', 'Bristol', 'hybrid', 5, 34.60, 5, 'completed', '2025-04-02'),
(7, 'Oliver Trent', 'Bristol', 'road', 4, 20.00, 0, 'completed', '2025-04-09'),
(8, 'George Mellor', 'Bristol', 'road', 4, 20.00, 0, 'completed', '2025-05-14'),
(9, 'Hannah Pryce', 'Bristol', 'tandem', 3, 65.00, 0, 'active', NULL),
(10, 'Zainab Yusuf', 'Cardiff', 'cargo', 7, 33.00, 10, 'completed', '2025-05-21'),
(11, 'Yusuf Kaya', 'Cardiff', 'e-bike', 6, 45.00, 0, 'completed', '2025-05-28'),
(12, 'Yusuf Kaya', 'Cardiff', 'e-bike', 6, 45.00, 5, 'completed', '2025-05-06'),
(13, 'Nadia Siddiqui', 'Cardiff', 'e-bike', 3, 45.00, 15, 'cancelled', NULL),
(14, 'Ibrahim Diallo', 'Cardiff', 'hybrid', 6, 32.00, 5, 'completed', '2025-04-27'),
(15, 'Oliver Trent', 'Cardiff', 'road', 6, 32.00, 0, 'completed', '2025-04-03'),
(16, 'Jade Okonkwo', 'Cardiff', 'road', 1, 32.00, NULL, 'cancelled', NULL),
(17, 'Kwame Mensah', 'Cardiff', 'tandem', 3, 26.00, 0, 'active', NULL),
(18, 'Rosa Delgado', 'Glasgow', 'cargo', 7, 11.00, 10, 'completed', '2025-05-23'),
(19, 'Leila Haddad', 'Glasgow', 'e-bike', 9, 25.00, 15, 'completed', '2025-05-09'),
(20, 'Callum Reid', 'Glasgow', 'hybrid', 3, 60.00, 10, 'completed', '2025-04-22'),
(21, 'Nadia Siddiqui', 'Glasgow', 'hybrid', 3, 48.00, 0, 'completed', '2025-05-30'),
(22, 'Rosa Delgado', 'Glasgow', 'hybrid', 2, 30.00, 0, 'completed', '2025-04-05'),
(23, 'Marcus Lindqvist', 'Glasgow', 'road', 8, 36.00, 15, 'completed', '2025-05-27'),
(24, 'Priya Shah', 'Glasgow', 'tandem', 4, 39.00, 10, 'completed', '2025-04-19');
The rows are ordered exactly as the query results will be discussed: the discount_pct column is a whole percentage or nothing at all, and returned_on is filled in only for hires that came back.
How many rows are there, really?
COUNT has three spellings that look identical until you meet a NULL. COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. All three, side by side on the rental book:
SELECT COUNT(*) AS rentals,
COUNT(discount_pct) AS with_discount,
COUNT(DISTINCT customer) AS customers,
COUNT(DISTINCT city) AS cities
FROM rentals;
rentals with_discount customers cities
------- ------------- --------- ------
24 22 18 3
The gap between 24 and 22 is the two rentals with no discount recorded — a NULL, not a zero. And 18 customers across 24 rentals tells you six people came back, which is the first hint that a customer-level report will be more interesting than a rental-level one. If you have ever written COUNT(col) expecting it to count rows, that gap is where the mistake hides.
What do SUM and AVG add up?
SUM adds a numeric column and AVG divides its total by the number of rows that contributed. Both are joined here by a derived revenue figure — days multiplied by the daily rate — because summing a rate column alone would answer a question nobody asked.
SELECT SUM(rental_days) AS total_days,
ROUND(SUM(rental_days * daily_rate), 2) AS gross_value,
ROUND(AVG(rental_days * daily_rate), 2) AS avg_value,
MIN(rental_days) AS shortest,
MAX(rental_days) AS longest
FROM rentals;
total_days gross_value avg_value shortest longest
---------- ----------- --------- -------- -------
118 4096.5 170.69 1 10
About a hundred and seventy pounds per rental on average across the book, with the longest hire running ten days. MIN and MAX complete the set: they are the cheapest and the extreme of any column, and unlike SUM and AVG they work perfectly well on text.
SELECT MIN(customer) AS first_alphabetically,
MAX(customer) AS last_alphabetically
FROM rentals;
first_alphabetically last_alphabetically
-------------------- --------------------
Aisha Rahman Zainab Yusuf
Aisha sorts first and Zainab last, which is what you would expect from the alphabet — and worth confirming, because on a non-alphabetical collation SQL Server and PostgreSQL can order the same words differently.
Why did the average come out as 4?
Here is the trap that catches nearly everyone. Divide two integers and SQL truncates the result toward zero, which means the average rental length silently loses its fraction:
SELECT SUM(rental_days) AS total_days,
COUNT(*) AS rentals,
SUM(rental_days) / COUNT(*) AS integer_average,
ROUND(SUM(rental_days) * 1.0 / COUNT(*), 2) AS true_average
FROM rentals;
total_days rentals integer_average true_average
---------- ------- ---------------- -------------
118 24 4 4.92
Four, when the real answer is 4.92 days. The multiplication by 1.0 promotes one operand to a REAL and the division behaves honestly; ROUND then decides how many decimals to show. The same trap applies to every aggregate over integer columns, and it is worth internalising as a rule: if a division can produce a fraction, promote one side before you divide.
What do aggregates do with NULL?
They ignore it — and that is not always what you want. COUNT is the exception, because counting a column is precisely a request to count the non-NULL values. The other aggregates skip missing rows entirely, which means their denominators shrink:
SELECT COUNT(*) AS rows_total,
COUNT(returned_on) AS rows_with_return,
COUNT(*) - COUNT(returned_on) AS rows_missing
FROM rentals;
rows_total rows_with_return rows_missing
---------- ----------------- ------------
24 19 5
Five rentals have no return date: two were cancelled and three are still out. If you average a column where those five rows hold NULL, the average describes nineteen rentals, not twenty-four — a fact you should either state in the report or correct with COALESCE, the function lesson 15 introduced. An aggregate over an empty set is not an error either: SUM returns NULL and COUNT returns 0.
Quick reference
- COUNT(*) counts rows, COUNT(col) counts non-NULL values, COUNT(DISTINCT col) counts distinct values.
- SUM and AVG ignore NULL rows entirely, so their denominators are the rows that had data.
- Integer over integer truncates: multiply by 1.0 before dividing when a fraction is possible.
- MIN and MAX work on numbers and on text, using the column's collation for strings.
- An aggregate over zero rows gives COUNT = 0 and SUM = NULL — handle both in report code.
Totals drill: using only aggregate functions on the rentals table, report the number of rentals, the total and average rental length in days, and the number of distinct customers — then write the average again in a form that a reader could not misread as a truncated integer.