The COUNT, the SUM and the AVG are the three answers to the question of what happened in this pile of rows. One of them counts them, one of them adds them up, and one of them 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 the NULL and the integer division make that collapse quietly lossy if you do not watch them.
This is the first lesson in the SQL GROUP BY and Aggregate Functions module, and the next lesson in this module is about "How Does GROUP BY Collapse Rows Into Summaries?".
What do the COUNT, the SUM and the AVG summarize?
The cycle-hire company in this module keeps one row per rental. A single row tells you that 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 they 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. There is one flat fact table, and every question is answered by collapsing it in a different way. Twenty-four rows load it. There are three cities, there are five kinds of bike, and there is a mix of the completed, the cancelled and the 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 column discount_pct is a whole percentage or nothing at all, and the returned_on is filled in only for the hires that came back.
How many rows are there, really?
The COUNT has three spellings that look identical until you meet a NULL. The COUNT(*) counts the rows. The COUNT(column) counts the rows where that column is not a NULL. The COUNT(DISTINCT column) counts the distinct non-NULL values.
All three of them, 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 3The gap between the 24 and the 22 is the two rentals with no discount recorded. That is a NULL and not a zero. And the 18 customers across the 24 rentals tells you that 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 a COUNT(col) expecting it to count the rows, that gap is where the mistake hides.
What do the SUM and the AVG add up?
The SUM adds a numeric column, and the AVG divides its total by the number of the rows that contributed. Both of them are joined here by a derived revenue figure, which is the days multiplied by the daily rate, because summing a rate column on its own would answer a question that 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 10That is about a hundred and seventy pounds per rental on average across the book, with the longest hire running for ten days.
The MIN and the MAX complete the set. They are the cheapest and the extreme of any column, and unlike the SUM and the AVG they work perfectly well on the text.
SELECT MIN(customer) AS first_alphabetically,
MAX(customer) AS last_alphabetically
FROM rentals;first_alphabetically last_alphabetically
-------------------- --------------------
Aisha Rahman Zainab YusufAisha sorts first and Zainab last, which is what you would expect from the alphabet. It is worth confirming, because on a non-alphabetical collation the SQL Server and PostgreSQL can order the same words differently.
Why did the average come out as the 4?
Here is the trap that catches nearly everyone. You divide two integers, and the SQL truncates the result toward the zero, which means that 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.92It is the 4, when the real answer is the 4.92 days. The multiplication by 1.0 promotes one operand to a REAL, and then the division behaves honestly. The ROUND then decides how many decimals to show.
The same trap applies to every aggregate over the integer columns, and it is worth internalising as a rule. If a division can produce a fraction, then you promote one side before you divide.
What do the aggregates do with the NULL?
They ignore it, and that is not always what you want. The COUNT is the exception, because counting a column is precisely a request to count the non-NULL values. The other aggregates skip the missing rows entirely, which means that 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 5There are five rentals with no return date. Two of them were cancelled and three are still out.
If you average a column where those five rows hold a NULL, then the average describes nineteen rentals and not twenty-four. That is a fact that you should either state in the report or correct with the COALESCE, which is the function that lesson 15 introduced.
An aggregate over an empty set is not an error either. The SUM returns a NULL, and the COUNT returns the 0.
Quick reference
- The COUNT(*) counts the rows, the COUNT(col) counts the non-NULL values, and the COUNT(DISTINCT col) counts the distinct values.
- The SUM and the AVG ignore the NULL rows entirely, so their denominators are the rows that had the data.
- The integer over the integer truncates, so you multiply by 1.0 before dividing when a fraction is possible.
- The MIN and the MAX work on the numbers and on the text, using the collation of the column for the strings.
- An aggregate over the zero rows gives a COUNT of 0 and a SUM of NULL, and you handle both of them in the report code.
Totals drill: using only the aggregate functions on the rentals table, you report the number of the rentals, the total and the average rental length in days, and the number of the distinct customers. Then you write the average again in a form that a reader could not misread as a truncated integer.