◀ Course contents Part 2 · Module 2-01

COUNT, SUM & AVG

Turning a table of rows into a single number

Part 1 was about choosing rows. This part is about summarising them, and it starts with the five functions that collapse many rows into one value. They are easy to write and easy to write wrongly: two of them disagree about what counting even means, and all but one of them quietly ignore the rows they cannot use.

Ready?

An Aggregate Collapses the Result

Every query in Part 1 returned rows: fewer of them, in a different order, with different columns, but rows. An aggregate function does something different in kind. It reads the whole set of rows and returns one value.

SELECT COUNT(*)
FROM orders;

That returns a single row with a single column in it. Not 24 rows with a 1 in each — one row, holding 24. The result has stopped being a list of orders and become a fact about the list.

There are five of these, and between them they answer most questions anyone asks of a table: COUNT how many, SUM the total, AVG the mean, and MIN / MAX the extremes.

The clause order does not change. WHERE still runs first and still throws rows away; the aggregate then works on whatever survived. This is why you filter a total by adding a WHERE clause rather than by changing the SUM — the aggregate has no idea which rows you consider real.

The filter runs before the aggregate, every time. To total part of a table, cut the table down and let the aggregate see only what is left — there is no way to tell SUM which of the rows it was handed to take seriously.

Everyday example

A shoebox of receipts. Sorting them, or pulling out only the ones from March, still leaves you with receipts. Adding them up leaves you with a number, and the receipts are gone from your hand. The pile is still in the shoebox — you did not destroy anything — but what you are now holding cannot tell you what any individual purchase was.

Quick check

SELECT COUNT(*) FROM orders WHERE status = 'refunded'; — the table has 24 orders and 2 refunds. What comes back?

COUNT(*), COUNT(column) and COUNT(DISTINCT)

COUNT looks like one function. It is three, and they answer three different questions.

COUNT(*) counts rows. It never looks inside them, so nothing in the data can change its answer.

COUNT(column) counts values in that column, and NULL is not a value — every NULL is skipped. On a column that is never empty the two agree exactly, which is precisely why this difference stays invisible right up until the day it matters.

SELECT COUNT(*)       AS all_rows,      -- 12
       COUNT(company) AS with_company   -- 7
FROM users;

Five of the twelve users signed up without a company, so those five rows have NULL there and drop out of the second count. Neither number is wrong. They are answers to different questions, and the bug is asking one and reporting the other.

COUNT(DISTINCT column) counts different values, each one once. This is the one people forget. orders has 24 rows and 10 buyers; "how many customers ordered" answered with COUNT(*) overstates the business by more than double.

How many records

`COUNT(*)`

24 orders. Rows, whatever is in them. The only COUNT that cannot be affected by NULLs.

How many have it

`COUNT(company)`

7 of 12 users. Non-NULL values only, which makes the gap a free data-quality report.

Usually the one meant

`COUNT(DISTINCT user_id)`

10 buyers, not 24. "How many customers" is almost never a row count, and answering it with one is a silent overstatement.

The report that doubled the customer base

A weekly deck carried "customers who bought this week", taken from a COUNT(*) over the orders table. It was right in week one, when nobody had ordered twice yet. It drifted upward for a year as repeat buyers accumulated, and nobody questioned it because the line went the direction everyone wanted. The query was never wrong about what it counted; it was answering a question nobody had asked.

Quick check

A signups table has 500 rows. COUNT(referrer) returns 380. What do you now know?

SUM and AVG Skip What They Cannot Use

SUM(column) adds a numeric column up. AVG(column) is that sum divided by the count of the same column. Both skip NULLs entirely, and that second sentence is where the trouble is.

If a rating column has 1000 rows and 400 of them are NULL, AVG(rating) is the average of 600 ratings. It is not the average of 1000 with the blanks as zero, and it is not an error. It is a correct average of a different population from the one you probably had in mind, reported with no warning attached.

Decide what a missing value means before you average it. A missing rating is unknown and should be skipped. A missing discount almost certainly means zero, and skipping it inflates the average discount of every order that had one.

SELECT ROUND(AVG(amount), 2) AS avg_order
FROM orders
WHERE status = 'paid';

MIN and MAX take the same shape and are not limited to numbers — on text they mean alphabetically first and last, and on a date column stored as text in YYYY-MM-DD they mean earliest and latest, which is how you find the oldest account on file without sorting anything.

ROUND(value, 2) exists because an average almost never comes back at a length anyone wants to read. It changes what is displayed, not what is stored.

Averages hide their own shape

One order of 250 and twenty of 12 average out to about 23, and no reader of that number would guess either figure. An average is a summary of a distribution, not a description of it — which is why MIN, MAX and a count usually belong next to it rather than after it.

Quick check

A discount column is NULL when no discount was given. Marketing asks for the average discount across all orders. What does plain AVG(discount) give them?

Zero and NULL Are Different Answers

Filter a table down to no rows at all and the aggregate still returns one row. What is in it depends on which function you used:

SELECT COUNT(*)    AS n,      -- 0
       SUM(amount) AS total   -- NULL
FROM orders
WHERE status = 'cancelled';

This is not a quirk to memorise. It is the honest answer to each question. How many matched? None — and none is a number. What do they add up to? There is nothing to add, so there is no total, and NULL is how SQL says "no value here".

It is also a real bug. A dashboard tile that divides by a SUM shows blank instead of zero on the first quiet day, and a nightly job that compares the total to a threshold silently does nothing, because every comparison with NULL is unknown — exactly as module 1-05 warned.

COALESCE(a, b) returns the first of its arguments that is not NULL, so COALESCE(SUM(amount), 0) reads as "the total, or zero if there was nothing to total".

Right place

At the edge, for display

COALESCE(SUM(amount), 0) AS revenue in the report someone reads. An empty cell where a number belongs looks broken.

Wrong place

Mid-calculation, before an average

AVG(COALESCE(rating, 0)) turns every unrated row into a nought-star review. The number is no longer unknown; it is wrong.

The alert that never fired

A job checked SUM(amount) < 1000 each morning and paged the team when takings were low. On the one morning takings were actually nothing, the SUM was NULL, the comparison was unknown rather than true, and no page was sent. The alert had worked for two years and failed on the only day it was for. Wrapping the SUM in COALESCE(..., 0) was a one-word fix.

Quick check

A query filters to a status that no row has, then runs COUNT(*) and MAX(amount). How many rows come back, and what is in them?

0 of 9 completed

Loading the tables…

To do

Everything so far has returned rows. An aggregate function does the opposite: it reads many rows and hands back one value.

COUNT(*) is the simplest. It counts rows — not values, not columns, rows — and it does not care what is in them.

SELECT COUNT(*)
FROM products;

One row comes back, with one column in it. That is the shape of every aggregate query until GROUP BY arrives in the next module.

Your task: return the number of rows in the users table, as a single column named total.

query.sql

-- One number, one column, named total.
SELECT
FROM users;

PostgreSQL

Hint

SELECT COUNT(*) AS total FROM users; — the AS names the output column, exactly as it did in module 1-04.

Output

To do

COUNT(*) counts rows. COUNT(column) counts values in that column — and NULL is not a value, so every NULL is skipped.

On a column with no NULLs the two agree, which is exactly why the difference stays hidden until it matters. In this database users.company is NULL for everyone who signed up without one.

Your task: return both numbers side by side from users — COUNT(*) as all_rows, and the count of company as with_company.

query.sql

-- Two counts, one row. They will not be the same number.
SELECT
FROM users;

PostgreSQL

Hint

SELECT COUNT(*) AS all_rows, COUNT(company) AS with_company FROM users; — two aggregates in one SELECT list is perfectly normal.

Output

To do

orders has 24 rows, but not 24 customers — plenty of people ordered more than once. "How many rows" and "how many different people" are different questions, and COUNT answers the second one only if you ask it to.

COUNT(DISTINCT country)

DISTINCT inside the brackets de-duplicates before counting. This is the single most common thing an analyst gets asked for and the single most common thing they forget to write.

Your task: return how many different users have placed an order, as one column named buyers.

query.sql

-- Not 24. How many different people are behind those 24 rows?
SELECT
FROM orders;

PostgreSQL

Hint

SELECT COUNT(DISTINCT user_id) AS buyers FROM orders; — DISTINCT goes inside the brackets, before the column name.

Output

To do

SUM() adds a numeric column across every row it is given. It ignores NULLs the same way COUNT(column) does, and it refuses text outright rather than guessing.

Your task: return the total of the amount column across every order, as one column named revenue.

query.sql

SELECT
FROM orders;

PostgreSQL

Hint

SELECT SUM(amount) AS revenue FROM orders;

Output

To do

WHERE runs first and throws rows away; the aggregate then works on whatever survived. So the way to total only part of a table is to filter it, not to change the SUM.

That 1951 from the last exercise included a pending order and two refunds. Nobody in finance means that when they say revenue.

Your task: return the total amount of orders whose status is paid, as one column named revenue.

query.sql

SELECT
FROM orders
WHERE

PostgreSQL

Hint

SELECT SUM(amount) AS revenue FROM orders WHERE status = 'paid'; — single quotes round the text, as always.

Output

To do

AVG() is SUM() over COUNT() of the same column — and, like both of them, it skips NULLs. That matters more than it sounds: an average over a column that is half empty is an average of the half that is there, and it will not warn you.

Averages also arrive with more decimal places than anyone wants. ROUND(value, 2) cuts them to two.

Your task: return the average amount of paid orders, rounded to two decimal places, as one column named avg_order.

query.sql

-- Paid orders only, rounded to 2 decimal places.
SELECT
FROM orders
WHERE

PostgreSQL

Hint

SELECT ROUND(AVG(amount), 2) AS avg_order FROM orders WHERE status = 'paid'; — ROUND wraps the whole AVG call.

Output

To do

MIN() and MAX() return the smallest and largest value in a column. They work on text and dates too, where "smallest" means alphabetically first and earliest — which is why MIN(signup_date) is how you find the oldest account.

Your task: from products, return the lowest price as cheapest and the highest as dearest, in that order.

query.sql

SELECT
FROM products;

PostgreSQL

Hint

SELECT MIN(price) AS cheapest, MAX(price) AS dearest FROM products;

Output

To do

Filter a table down to nothing and the aggregates still return one row. What is in that row is the part worth knowing: COUNT gives 0, and SUM gives NULL.

That is not a quirk to memorise, it is the honest answer to each question. "How many matched?" — none, which is a number. "What do they add up to?" — there is nothing to add, which is not a number.

It is also a real bug: a dashboard that divides by a SUM shows blank rather than zero on the day nothing sold.

Your task: there are no orders with status cancelled. Return COUNT(*) as n and SUM(amount) as total for that status, and look at what comes back.

query.sql

-- No order has this status. Both aggregates still return something.
SELECT
FROM orders
WHERE status = 'cancelled';

PostgreSQL

Hint

SELECT COUNT(*) AS n, SUM(amount) AS total FROM orders WHERE status = 'cancelled'; — the result is one row: 0, and an empty cell.

Output

To do

Sometimes NULL is the honest answer and you still cannot use it — a report with an empty cell where a number belongs reads as broken, not as precise.

COALESCE(a, b) returns the first of its arguments that is not NULL, so COALESCE(SUM(amount), 0) says "the total, or zero if there was nothing to total".

Do it at the edge, when you present the number — not in the middle of a calculation, where replacing "unknown" with zero is how a wrong average gets published.

Your task: the same cancelled-orders query, but return COUNT(*) as n and the total as total showing 0 rather than an empty cell.

query.sql

SELECT COUNT(*) AS n,
       -- make this one show 0 instead of nothing
FROM orders
WHERE status = 'cancelled';

PostgreSQL

Hint

COALESCE(SUM(amount), 0) AS total — COALESCE wraps the SUM, not the other way round.

Output

The one-line summary

To do

Someone wants the state of the business on one line. Not a table of orders — one row, four numbers, and no explanation needed underneath it.

They mean paid orders. The pending one has not been collected and the two refunds went back out, so neither belongs in a revenue figure.

Your task: from orders, over paid rows only, return exactly these four columns in this order:

orders — how many there are
buyers — how many different users are behind them (fewer than have ever ordered: one person's only order was refunded)
revenue — the total amount
avg_order — the average amount, rounded to two decimal places

One row, four columns, named exactly as above.

query.sql

-- Paid orders only. Four numbers, one row.
SELECT
FROM orders
WHERE

PostgreSQL

Hint

COUNT(*) AS orders, COUNT(DISTINCT user_id) AS buyers, SUM(amount) AS revenue, ROUND(AVG(amount), 2) AS avg_order — then WHERE status = 'paid'.

Output

Sign in to open this module

COUNT, SUM & AVG

An Aggregate Collapses the Result

Everyday example

Quick check

COUNT(*), COUNT(column) and COUNT(DISTINCT)

COUNT(*)

COUNT(company)

COUNT(DISTINCT user_id)

The report that doubled the customer base

Quick check

SUM and AVG Skip What They Cannot Use

Averages hide their own shape

Quick check

Zero and NULL Are Different Answers

At the edge, for display

Mid-calculation, before an average

The alert that never fired

Quick check

Count the rows

COUNT(*) and COUNT(column) disagree

Count the different ones

Add a column up

Aggregate what WHERE left behind

The average order

The extremes

Zero and NULL are different answers

Turn that NULL into a zero

The one-line summary

Notification

`COUNT(*)`

`COUNT(company)`

`COUNT(DISTINCT user_id)`