BrevFeed
Sign in

21. Aggregate functions: COUNT, SUM, AVG and friends

📖 Reading · 6 min
💡 Every code box below is live — edit it and hit Run.

What makes a function "aggregate"

Every function you've used so far (computed columns, upper(), arithmetic) is row-by-row — one input row produces one output value, independently of every other row. An aggregate function is fundamentally different: it consumes many rows and collapses them into a single value. This is the first genuinely new computational shape in the course, and it's the foundation for GROUP BY in the next lesson.

SELECT count(*) AS total_films FROM film;        -- one row out, from 1000 rows in
SELECT avg(rental_rate) AS avg_price FROM film;   -- one row out, from 1000 rows in

Without a GROUP BY clause (next lesson), an aggregate collapses the entire table (or filtered result) into one row. This is why SELECT title, avg(rental_rate) FROM film is an error — title wants one value per row, avg(rental_rate) wants to collapse all rows into one; mixing an ungrouped column with an aggregate is a contradiction the engine rejects outright.

The five you'll use constantly

SELECT
    count(*)              AS total_films,
    sum(rental_rate)       AS total_of_all_rates,
    avg(rental_rate)       AS average_rate,
    min(rental_rate)       AS cheapest,
    max(rental_rate)       AS most_expensive
FROM film;

SUM/AVG require a numeric-ish input; MIN/MAX work on anything orderable (numbers, text, dates) — MIN(title) returns the alphabetically-first title, MAX(rental_date) returns the most recent rental.

COUNT(*) vs COUNT(column) — a real, common gotcha

count(*) counts rows, full stop — every row, NULLs and all. count(column) counts non-NULL values in that column specifically — aggregates skip NULL (the "unknown" value from Module 1). These are not the same question, and the difference matters the moment a column can be NULL:

-- Every rental has a rental_id, so these two agree here:
SELECT count(*) AS all_rows, count(rental_id) AS non_null_rental_ids FROM rental;

-- return_date CAN be NULL (a rental not yet returned) — these DIVERGE:
SELECT count(*) AS all_rentals, count(return_date) AS returned_rentals FROM rental;
-- The gap between these two numbers is exactly "rentals not yet returned."

That gap is itself a useful, real number — count(*) - count(return_date) directly answers "how many outstanding rentals are there," entirely as a side effect of understanding what each COUNT variant actually counts.

count(DISTINCT column) counts distinct non-NULL values — combining the DISTINCT dedup behavior from Module 1 with aggregation:

SELECT count(DISTINCT rating) AS distinct_ratings FROM film;   -- 5
SELECT count(rating) AS total_rating_values FROM film;         -- 1000 (every film has a rating)

Aggregates and three-valued logic, together

A subtlety worth internalizing now rather than debugging later: SUM, AVG, MIN, MAX on a column with zero non-NULL rows return NULL, not zero. This trips people up constantly when the aggregate result feeds into further arithmetic:

-- If no rows matched this filter at all, sum() returns NULL, not 0.
-- (film_id = -1 matches no row by construction — film_id is a positive serial.)
SELECT sum(rental_rate) FROM film WHERE film_id = -1;
-- result: NULL — there were zero rows to sum, so "the sum" is genuinely unknown/undefined,
-- not "zero dollars"

(Aside: pagila's rating column is actually a Postgres enum type [mpaa_rating], not plain text — trying to compare it against a string that isn't one of its five defined values raises a real error rather than just matching zero rows. Custom enum types aren't covered further in this course; this is just a heads-up so rating = 'something-made-up' doesn't surprise you with an error instead of an empty result the way it would on a genuinely text-typed column.)

COALESCE(sum(rental_rate), 0) (Module 1's NULL lesson) is the standard guard when you specifically want "zero" to mean "nothing matched," e.g. in a financial report where a blank should read as $0.00, not a blank cell.

Check yourself

  1. What's the difference between count(*) and count(some_column), precisely?
  2. If a filter matches zero rows, what does SUM(...) over that empty result return — zero, or something else?
  3. Using rental.return_date, write a single query that reports both the total rental count and the count of outstanding (not-yet-returned) rentals, without a second query.
← Previous Next →