COUNT, SUM, AVG, MIN and MAX

An aggregate takes many rows and returns one value. Five of them do almost all the work, and the one thing worth understanding properly is what they do about NULL.

The five

SELECT count(*)          AS books,
       sum(copies)       AS total_copies,
       round(avg(price_paise) / 100.0, 2) AS avg_rupees,
       min(price_paise)  AS cheapest,
       max(price_paise)  AS dearest
FROM books;

 books | total_copies | avg_rupees | cheapest | dearest
-------+--------------+------------+----------+---------
    40 |           95 |     314.63 |    17500 |   69900
(1 row)

One row out, forty rows in. That is the whole idea. With no GROUP BY, the entire table is one group.

count counts, sum adds, avg averages, min and max find the extremes. min and max work on text and dates too — max(borrowed_on) is the most recent loan.

count(*) versus count(column) versus count(DISTINCT column)

Three different questions, and the difference matters.

SELECT count(*)                  AS loan_rows,
       count(DISTINCT member_id) AS distinct_members,
       count(DISTINCT book_id)   AS distinct_books
FROM loans;

 loan_rows | distinct_members | distinct_books
-----------+------------------+----------------
       180 |               28 |             39
(1 row)

180 loans, made by 28 different members, of 39 different books. There are 40 books, so one book has never been borrowed — and that single number is a genuinely useful thing to know.

• count(*) — how many rows. Never NULL, never skips anything.
• count(column) — how many rows where that column is not NULL.
• count(DISTINCT column) — how many different non-NULL values.

count(*) is also the fastest, because it does not have to look at any column's value. Use count(*) when you mean "how many rows", which is nearly always.

Every aggregate except count(*) ignores NULL

This is the property to internalise, because it decides whether your number is right.

SELECT count(*)              AS all_loans,
       count(fine_paise)     AS with_fine_value,
       sum(fine_paise)       AS total_fine,
       round(avg(fine_paise), 2) AS avg_fine
FROM loans;

 all_loans | with_fine_value | total_fine | avg_fine
-----------+-----------------+------------+----------
       180 |             136 |     263000 |  1933.82
(1 row)

180 loans; only 136 have a fine value at all. The 44 still out have fine_paise as NULL, because the fine is not yet knowable.

So avg_fine is the average over 136 rows, not 180. Nothing in that output says so.

Watch what happens if you decide the unknowns should count as zero:

SELECT round(avg(fine_paise), 2)               AS avg_ignoring_null,
       round(avg(COALESCE(fine_paise, 0)), 2)  AS avg_treating_null_as_zero
FROM loans;

 avg_ignoring_null | avg_treating_null_as_zero
-------------------+---------------------------
           1933.82 |                   1461.11
(1 row)

₹19.34 against ₹14.61. A 32% difference, from one decision about what NULL means, with no error either way.

Which is correct depends entirely on the question:

• "What is the average fine on a returned loan?" → avg(fine_paise), the first.
• "What does the average loan cost us in fines?" → COALESCE(..., 0), the second — but arguably you should exclude the unreturned ones entirely, since they have not finished.

Always ask what the denominator is. An average with an unexamined denominator is the commonest way a report is quietly wrong.

sum of nothing is NULL, not zero

SELECT sum(fine_paise) FROM loans WHERE 1 = 0;   -- NULL, not 0
SELECT count(*)        FROM loans WHERE 1 = 0;   -- 0

An empty sum is NULL. This bites in application code, where NULL becomes None or null and then something adds to it. COALESCE(sum(fine_paise), 0) if you need a number.

count is the exception: it returns 0, because "how many rows" is knowable and the answer is none.

FILTER, which replaces a pile of CASE

SELECT count(*)                                    AS total,
       count(*) FILTER (WHERE returned_on IS NULL) AS still_out,
       count(*) FILTER (WHERE fine_paise > 0)      AS fined
FROM loans;

 total | still_out | fined
-------+-----------+-------
   180 |        44 |    57
(1 row)

Three different questions, one pass over the table. Before FILTER you wrote sum(CASE WHEN ... THEN 1 ELSE 0 END), which you will still see everywhere and which does the same thing less readably.

FILTER is standard SQL and works with any aggregate:

sum(fine_paise) FILTER (WHERE membership = 'student')
avg(price_paise) FILTER (WHERE published < DATE '1960-01-01')

This is one of the highest-value things in this module. A dashboard query that used to be five separate round trips becomes one.

Rounding, and what avg returns

SELECT avg(price_paise) FROM books;

avg on an integer column returns numeric — arbitrary precision — which prints at full length. round(x, 2) gives two decimal places, and round() on numeric takes that second argument. On a double precision it does not, which is one more reason to keep money as an integer of paise and divide only for display.

What you cannot do yet

SELECT title, count(*) FROM books;

ERROR:  column "books.title" must appear in the GROUP BY clause or be used in an
        aggregate function

You asked for one row (an aggregate over everything) and forty rows (a title each). The database cannot give both, and it says so precisely.

That error is the single most common one in SQL, and the next lesson is entirely about the clause that fixes it.

Check your work

What an aggregate does. Many rows in, one value out — and with no GROUP BY the whole table is one group.

The three counts. count(*) is rows; count(col) is non-NULL values; count(DISTINCT col) is different non-NULL values.

Which is fastest, and when to use it. count(*), and whenever you mean "how many rows".

What 180 / 28 / 39 told us. One book has never been borrowed.

What every aggregate except count(*) does with NULL. Ignores it.

Why the two averages differed by 32%. One averaged over 136 rows, the other over 180.

The question to always ask. What is the denominator?

What sum of no rows returns. NULL, not zero — count returns zero.

What FILTER buys. Several different questions in one pass over the table.

What it replaced. sum(CASE WHEN ... THEN 1 ELSE 0 END).

Why avg prints so many decimals. It returns numeric; use round(x, 2).

The most common error in SQL. Selecting a plain column alongside an aggregate.

Practice

1. Count the books, sum the copies, and find the cheapest and dearest.
2. Compute count(*), count(price_paise) and count(DISTINCT shelf) on books.
3. Work out from the loans table how many books have never been borrowed. Then find which one.
4. Compare avg(fine_paise) with avg(COALESCE(fine_paise, 0)) and explain both.
5. Write down, for each, the question it answers.
6. Compute sum(fine_paise) over a WHERE that matches nothing. Then count(*) over the same.
7. Wrap that sum in COALESCE and explain why you would.
8. Use FILTER to get, in one query: total loans, loans still out, loans returned late, and loans with no fine.
9. Rewrite one of those FILTERs as sum(CASE WHEN ...) and compare readability.
10. Find the earliest and latest borrowed_on with min and max.
11. Use min and max on title and explain the result.
12. Compute the average price in rupees to two decimal places.

Official documentation

• PostgreSQL — Aggregate functions — Every aggregate, with the note that all except count(*) ignore NULL.
• PostgreSQL — Aggregate expressions — Including FILTER and DISTINCT inside an aggregate.
• PostgreSQL — Mathematical functions — round() and which types it accepts a precision argument for.

Next: GROUP BY, the concept that trips everyone up.