Aggregate Functions: COUNT, SUM, AVG, MIN, MAX — Turning Many Rows Into One Number

The situation this lesson prepares you for

Somebody stops at your desk. "How many orders came in?" "What did we spend on shipping last quarter?" "What's our most expensive product?"

Every one of those questions has one number as its answer.

Everything you have written so far hands back rows. SELECT picks columns, WHERE keeps rows, ORDER BY arranges them, LIMIT cuts them — and at the end of all of it you still have rows. This lesson is the first time a query hands back a value.

The five functions that do it are COUNT, SUM, AVG, MIN and MAX. They are called aggregate functions, and each one reads a whole pile of rows and returns a single answer about the pile.

They are also the first thing in this course that can hand you a confidently wrong number. Not an error — a number, correctly calculated, about a set of rows that is not the set you had in mind. By the end of this lesson you will know the two ways that happens and how to check for both.

Every query below runs on the Northwind database on PostgreSQL. Type each one into CloudBeaver and compare the result with the table under it. If that is not set up yet, the Learn Data guide gets it running.

Many rows in, one number out

SELECT count(*) AS "Orders"
FROM "Orders";

One row. One column. 79 orders.

Look at what just happened to the shape of the result. The "Orders" table has 79 rows in it, and this query returned one. That is what "aggregate" means: many rows go in, one value comes out.

count(*) counts rows. The * is not "all columns" here the way it is in SELECT * — it means count the row itself, whatever is in it. A row with every field empty still counts as a row.

And note the alias. Without AS "Orders" your database picks a heading of its own — PostgreSQL calls the column count — which is fine for you and useless for anybody you send it to. Aliasing an aggregate is not decoration; it is the difference between a number and a labelled number.

MIN and MAX: the two ends of a column

SELECT min("UnitPrice") AS "Cheapest",
       max("UnitPrice") AS "Dearest"
FROM "Products";

Two aggregates in one SELECT, and both describe the same 20 rows. You can put as many as you like in one query; each one reads the same pile and answers its own question about it.

MIN and MAX are not only for numbers. They work on anything the database knows how to order — which, after the sorting lesson, you already have a feel for:

SELECT min("OrderDate") AS "First order",
       max("OrderDate") AS "Latest order"
FROM "Orders";

On a date column, MIN is earliest and MAX is latest. On a text column they are first and last alphabetically — and the same warning from the data types lesson applies, because a number stored as text still sorts like a dictionary. MAX of a text column of amounts hands you whichever one starts with the highest digit, not the biggest amount.

SUM: adding a column up

SELECT sum("Freight") AS "Total freight"
FROM "Orders";

That is what the company has paid couriers across all 79 orders. SUM is the only one of the five that insists on a number — you cannot add up a column of dates or company names, and it will tell you so.

Watch what you are adding up. SUM will happily total any numeric column, including ones where a total means nothing at all. sum("UnitPrice") over the product catalogue returns 574.39, which is a real number and answers no question anybody has: it is every price tag added together. Before you write SUM, say the answer out loud as a sentence. If the sentence is nonsense, the query is nonsense.

SUM also takes an expression, not just a column — so with what you learned last lesson you can add up something the table does not store:

SELECT sum("UnitPrice" * "UnitsInStock")
         AS "Stock value"
FROM "Products";

Price times units in stock, multiplied out on every row and then added up. That is what the shelves are worth, and it is not a column anywhere in the database.

AVG: the total over the count

SELECT avg("Freight") AS "Average freight"
FROM "Orders";

16 decimal places for a delivery charge.

That is not a bug, and the reason is worth understanding rather than tidying away. It is division.

MIN and MAX hand back a value that really is in the column. SUM computes something new, but addition cannot invent a digit that was not in one of the numbers it added — which is why the freight total came back as 3988.52 — still an ordinary money figure, the same shape as the numbers that went into it. AVG divides, and division is the one operation here that can produce digits your data never had.

So an average is a calculation with as many digits as the arithmetic produced. It is a full-precision number, not a money amount, and rounding it for presentation is a separate job from computing it.

And you already know how to fix it for a reader, because you met ROUND last lesson. The same rule applies here as applied there: you round the answer, not what went into it — so ROUND wraps the whole avg(...) call rather than sitting inside it.

SELECT ROUND(avg("Freight"), 2)
         AS "Average freight"
FROM "Orders";

Which also settles the caveat above: rounded to two places, both engines print exactly 50.49. Compute at full precision, round when you present.

The one thing to hold on to

Every number above is an answer about a set of rows. And two things decide which rows are in that set, neither of which you can see by looking at the answer:

1. Which rows survived WHERE.
2. Which values were actually there.

Get either wrong and you get a number, not an error. The rest of this lesson is those two, one at a time, and then what happens when the set turns out to be empty.

WHERE runs first

"What's our most expensive product?"

SELECT max("UnitPrice") AS "Dearest"
FROM "Products";

That is a true fact about the table, and it is the wrong answer to the question.

Look at what is actually up there. The "Products" table has a column this lesson has not needed until now:

SELECT "ProductName", "UnitPrice", "Discontinued"
FROM "Products"
ORDER BY "UnitPrice" DESC
LIMIT 2;

Both of them. Thuringer Rostbratwurst costs 123.79 and we do not sell it any more; neither do we sell Mishi Kobe Niku at 97. So the "most expensive product" is a sausage no customer can order, and it has been sitting at the top of that price list all along.

The "Products" table has a "Discontinued" column. Use it:

SELECT max("UnitPrice") AS "Dearest"
FROM "Products"
WHERE "Discontinued" = false;

38, not 123.79. Same table, same function, same column. The only difference is which rows the aggregate was allowed to see, and the answer fell by two thirds.

WHERE runs first, and the aggregate only ever sees what survived it. The row filter happens, and then the surviving rows are collapsed into one number.

That order is worth saying out loud, because it is what makes WHERE the most powerful line in an aggregate query.

It also explains a rule you are about to run into: you cannot put an aggregate inside WHERE. WHERE count(*) > 5 is rejected — at the moment WHERE runs, there is no count yet, because counting is what happens afterwards. The clause for filtering on an aggregate is HAVING, and it has its own lesson later in this series.

Once you are filtering, put everything you want to know in the one query:

SELECT count(*) AS "On sale",
       min("UnitPrice") AS "Cheapest",
       max("UnitPrice") AS "Dearest",
       avg("UnitPrice") AS "Average"
FROM "Products"
WHERE "Discontinued" = false;

18 products actually on sale, priced 4.5 to 38, averaging 19.64. All four aggregates describe the same 18 rows, because they all sit above the same WHERE. Over the full catalogue that average is 28.72 — 2 rows out of 20 were pulling it up by more than 9.

COUNT(*) counts rows. COUNT(column) counts values.

This is the one to remember.

SELECT count(*) AS "Orders",
       count("ShippedDate") AS "Shipped"
FROM "Orders";

Two counts. One table. No WHERE anywhere. 27 apart.

Nothing is broken. You met those 27 orders in the sorting lesson: they were placed and have never shipped, so their "ShippedDate" is empty. And:

count(*) counts rows. count(column) counts the values in that column — and an empty cell is not a value, so it is not counted.

Which means count("ShippedDate") is not "how many orders are there" at all. It is "how many orders have a shipped date", which is a completely different question that happens to be worth asking. Both numbers are right. Put either one under a heading that means the other and you have shipped a wrong number.

And every other aggregate does the same

SUM, AVG, MIN and MAX all skip missing values in exactly the same way.

On SUM, MIN and MAX that is almost always what you wanted — there is nothing to add, and nothing to compare. On AVG it moves the denominator, and that is where it stops being harmless:

AVG divides by the number of values it found, not by the number of rows.

You can see the size of that. The total freight bill is 3988.52. Divided by the 79 rows, that is 50.4876. Divided by 52 — the number of values "ShippedDate" has — it would be 76.7023. Same total, a denominator a third smaller, an answer half as big again. And the word AVG looks identical either way.

"Freight" itself is filled in on every order, so avg("Freight") really is the total over 79 and the two ways of writing it agree exactly. That agreement is a property of this column, not a rule. Check the column before you trust the average.

Two habits follow, and the second one is the one people skip.

Say which rows you mean. And print the count of the column you averaged, not just the row count — because count(*) is not what AVG divided by:

SELECT count(*) AS "Shipped orders",
       count("Freight") AS "Freight values",
       avg("Freight") AS "Average freight"
FROM "Orders"
WHERE "ShippedDate" IS NOT NULL;

Both counts are 52, so the average really did cover every one of those orders. That is the point of printing both. When the two numbers match, the average is over everything the filter kept. When they differ, you have just found the gap — and the difference is exactly how many rows the average silently left out.

Printing count(*) alone would have looked just as reassuring here, and it would have been telling you nothing: it happens to equal the denominator only because "Freight" has no empty values. Count the column you averaged.

Note the IS NOT NULL. That is the correct way to test whether a value is present — never write <> NULL. It looks perfectly reasonable and quietly matches nothing at all. Series 1 · 45 covers why; for now, use IS NULL and IS NOT NULL and trust them.

When the set is empty

We have never shipped an order to Japan. So what does this do?

SELECT count(*) AS "Orders",
       sum("Freight") AS "Total freight"
FROM "Orders"
WHERE "ShipCountry" = 'Japan';

WHERE threw away all 79 rows, and the query still returned one row.

That is the third thing an aggregate query does that no other query does: it always returns exactly one row, even when nothing matched. A SELECT with no aggregate would have returned nothing at all here — zero rows. This returns one row containing the answer "nothing matched".

And look at how the two functions say it differently:

• COUNT says 0. There were no rows, and zero of them is a real number.
• SUM says nothing at all. Not 0 — there was nothing to add up, so there is no total, and SQL does not invent one.

That second cell is worth looking at properly, because CloudBeaver labels it for you: it prints a greyed [NULL] rather than leaving the space blank. That is the client telling you the difference between a value that happens to be empty and no value at all — and it is the same distinction the NULL & Three-Valued Logic lesson is built on. For now, read [NULL] as "there is nothing here"; the word itself gets its own lesson.

AVG, MIN and MAX behave like SUM here: nothing, not zero. This is a genuine production bug waiting to happen — because most dashboards are not as honest as CloudBeaver. A tile that reads "Total freight: " with nothing after it, on the day a filter matches nothing, is the same [NULL] with its label taken off, and somebody assumed a sum always produces a number.

Counting the different values

"How many countries do we ship to?"

count("ShipCountry") will not answer that. It counts values, and Germany appears on 32 separate rows — so it would tell you 79, which is the number of orders.

SELECT count(DISTINCT "ShipCountry") AS "Countries",
       count("ShipCountry") AS "Orders"
FROM "Orders";

DISTINCT goes inside the brackets, in front of the column: count each different value once, however many rows carry it. 10 countries, across 79 orders.

You met DISTINCT last lesson as a thing that goes after SELECT and removes duplicate rows. This is the same idea in a different place, and the placement matters: SELECT DISTINCT count(...) is a different query that does not do this.

The wall: an aggregate and a plain column

Sooner or later everybody writes this. "The most expensive product is 123.79 — fine, tell me which one it is":

SELECT "ProductName",
       max("UnitPrice") AS "Dearest"
FROM "Products";

SQL Error [42803]: ERROR: column "Products.ProductName" must appear
in the GROUP BY clause or be used in an aggregate function
  Position: 8

An error, at last — and a helpful one, once you see what it is protecting you from.

max("UnitPrice") produces one value. "ProductName" has 20. There is no honest way to put 20 names in one row, so rather than pick one and pretend, the database refuses. Every engine does this; the wording differs, and they all point at the same clause.

There are two ways forward, and you already have one of them:

To name a single row, sort it and take one. This is the sorting lesson, and it is the right tool whenever you want the row rather than the number:

SELECT "ProductName", "UnitPrice"
FROM "Products"
ORDER BY "UnitPrice" DESC
LIMIT 1;

To get one number per group — per country, per customer, per month — you need GROUP BY, which is the very next lesson, and which is what that error message has been telling you all along.

Two more things worth knowing

count(1) is the same as count(*). You will meet it in older code, along with count('x') and other variations. They all count rows and they all perform identically; nothing is being optimised. Write count(*).

An aggregate with no rows underneath it is not the same as no result. Worth repeating because it is the one that reaches production: SELECT sum(...) FROM ... WHERE <matches nothing> gives you a row containing an empty cell, and whatever reads that row has to cope with an empty cell. A COUNT in the same query is the cheapest possible way to tell "nothing matched" from "the total really is zero".

What you can do now

For any request that ends in a single number:

1. Pick the function — how many (COUNT), how much in total (SUM), typical (AVG), the ends (MIN / MAX).
2. Say which rows with WHERE, before you count anything. The aggregate sees what survives.
3. Ask whether the column can be empty. If it can, count(column) and AVG are quietly working on a smaller set than you think — so put count("TheColumnYouAveraged") next to the answer. Not count(*): that is the row count, and it is not what AVG divided by.
4. Ask what happens when nothing matches. COUNT gives 0; the other four give you nothing at all.
5. Alias every aggregate, so the number arrives with its name attached.

And one question to ask out loud whenever you send somebody a total: how many rows is this number about? If you cannot answer that from the query, neither can they.

Next lesson: GROUP BY — the same five functions, but one number per customer, per country, per month, instead of one number for the whole table. It is the clause that error message kept pointing at, and it is where these five stop being trivia and start being reporting.

The koans: now you write the queries

Each koan is a tiny fill-in-the-blank exercise: a failing test with a ___ where the answer belongs. Fill it in, run it, watch red turn green — and the next one unlocks.

cd c:/DataPallas/db/datazeus
.\zeus.bat koans learnsql series1 _25

On macOS or Linux, run ./zeus.sh koans learnsql series1 _25 from the same folder. The three arguments are course, series and episode.

Says the lesson isn't there? Your copy of DataZeus predates this lesson — run .\zeus.bat update (or ./zeus.sh update) once to fetch the latest lessons, then run the command again. Koans you have already solved are kept.

The file you edit is:

c:\DataPallas\db\datazeus\tests\src\koans\groovy\datazeus\learnsql\series1\_25\AggregateFunctionsKoans.groovy

Open it in any editor — Notepad++, VS Code, whatever you have. Each koan has a ___ where the answer belongs. Replace one, save, re-run the command above, and watch that koan go green.