So far, every one of our queries has returned detail: one result row for each row in the database. That answers "what?" questions —which loans are open, which copies nobody has touched— but BiblioRed's management asks different ones: how many loans did we make last quarter?, which branch lends the most?, what is the star title?, how much have we collected in surcharges?, which members are over two loans?.

Those questions require moving from detail to summary: condensing many rows into a single figure. That is what the aggregate functions and the GROUP BY clause do, and it is the step that turns a database into a management tool.

This lesson also clears up something you have been dragging along since your very first query: the order in which an SQL statement is really executed. It is not the order in which it is written, and understanding it resolves half a dozen apparently inexplicable errors in one go.

We take the JOINs and subqueries from lesson 02-04 as read: we will use them without explaining them again.

1. The aggregate functions
2. COUNT and its three forms
3. SUM, AVG, MIN and MAX
4. How aggregates treat NULL values
5. GROUP BY: aggregating by groups
6. The rule about what can appear in the SELECT
7. GROUP BY on several columns
8. HAVING versus WHERE
9. The logical order of execution of a query
10. Aggregation combined with JOIN
11. The problem of rows inflated by a JOIN
12. COALESCE and CASE WHEN inside aggregates
13. Introduction to window functions
14. Common mistakes and tips
15. Exercises
16. Conclusion

An aggregate function takes many values and returns just one. The five in the SQL standard, present in every manager:

Without GROUP BY, an aggregate function treats the whole table as a single group and returns one row only:

You can combine several in the same query, as long as they all summarize the same set of rows:

Twelve loans between 2 March and 25 July 2026, with €7.00 collected in surcharges.

Notice an essential detail: the result has one row, not twelve. The query no longer talks about individual loans, it talks about the set. That is why you cannot freely mix aggregates with detail columns; we will see it in section 6.

COUNT looks like the simplest function and it is the one that causes the most confusion, because it has three variants that count different things.

The difference between the first two is the one most often overlooked, and in loans it is especially telling:

COUNT(return_date) counts 8 because the four open loans have that column at NULL. It is a very useful trick: counting the non-nulls of a column is the same as counting the rows that satisfy a certain condition, if that condition is reflected in the nullness.

And COUNT(DISTINCT member_id) returns 8, not 12: there are eight distinct members with loans, because some have more than one.

About performance: there is a legend that COUNT(1) is faster than COUNT(*). It is false in PostgreSQL and in any modern manager: they are identical. COUNT(DISTINCT column) is noticeably more expensive, because it forces deduplication.

ROUND(expression, decimals) is not an aggregate function: it rounds the result. Without it, AVG over NUMERIC in PostgreSQL returns a number with a great many decimal places (2000.8888888888888889), hardly practical for a report.

MIN and MAX also work on text (alphabetical order) and on dates (chronological order):

Watch out for a classic trap: this query tells you what the minimum last name is and what the minimum date is, but it does not tell you that they belong to the same person. Each aggregate is computed separately. To get the complete row of the longest-standing member you need something else:

It is the scalar subquery from the previous lesson, now with an aggregate inside. In section 13 we will see an alternative using window functions.

Here there is one rule and one exception, and both are worth burning in:

Every aggregate function ignores NULLs. The only exception is COUNT(*), which counts rows and does not look at the content.

Let's see it with the surcharge column, which has eight values and four NULLs:

Two different averages for the same data, and neither of them is wrong: they mean different things.

• AVG(surcharge) = 7.00 / 8 = €0.875. It is "the average surcharge of the loans already closed", because the open ones have no surcharge computed yet.
• SUM(surcharge) / COUNT(*) = 7.00 / 12 = €0.583. It is "the average surcharge per loan made", treating the pending ones as zero.

The question you must always ask yourself is: what does the NULL mean in this column? If it means "not known yet", ignoring it is correct. If it means "zero", ignoring it distorts the average. In section 12 we will see COALESCE, the tool for explicitly turning NULL into zero when that is the correct semantics.

One extreme case that comes as a surprise:

SUM over a set where every value is NULL returns NULL, not zero. And the same if the set is empty. COUNT, by contrast, returns 0: it is the only aggregate function that never returns NULL.

Until now we summarized the whole table. GROUP BY splits it into groups and computes the aggregates within each group, returning one row per group.

Four rows, one per branch, and the counts add up to 15: the total number of copies. Conceptually this is what happens:

Another example, grouping by a text column:

Notice that you can sort by the aggregate using its alias. And notice too that the groups come out of the data that exists: if no copy were in repair, that row simply would not appear. GROUP BY never invents empty groups.

A third example, with more than one aggregate per group:

This is the rule that produces the most errors when starting out:

Every column that appears in the SELECT and is not inside an aggregate function must be listed in the GROUP BY.

The reason is plain common sense. If you group the copies by branch, each result row represents six different copies (the ones at branch 1). Which code should it show? Which of the six? The question has no answer, so the manager rejects it:

The three legitimate ways to fix it, depending on what you really want:

Result of option b):

PostgreSQL, since version 9.1, allows a very practical relaxation: if you group by a table's primary key, you can select any other column of that same table without listing it, because the primary key determines it uniquely.

It is a real convenience, but it is not portable: in other managers you would have to write GROUP BY m.member_id, m.first_name, m.last_name. If your SQL has to work on several engines, list every column.

SQLite does not apply this rule at all. The query that raises an error in PostgreSQL runs in SQLite and returns any code from the group, chosen in an undocumented way. There is no error, there is no warning, and the report is wrong.

That EJ-3095 represents nothing. It is one of the most dangerous differences between the two managers: SQLite's permissiveness turns an error into a false piece of data. Write your SQL as if PostgreSQL were watching you, even when you are in SQLite.

When you group by two columns, the groups are the distinct combinations of both:

Eight groups, whose counts add up to 15. Just as before: only the combinations that exist appear. Branch 1 has no copy in repair, so that row is not there —it does not come out with a 0—. If you need the complete grid with zeros included, there are two routes: a CROSS JOIN that generates every combination (lesson 02-04) plus a LEFT JOIN against the data, or the CASE WHEN tabulation of section 12.

The order of the columns in the GROUP BY does not alter the groups (GROUP BY a, b and GROUP BY b, a produce the same ones), but it is worth making it match the ORDER BY so that the report reads well.

WHERE filters rows, before grouping. HAVING filters groups, after grouping. The difference is one of timing, not of syntax.

Question: which members have two or more loans?

Trying the same thing with WHERE is impossible:

And it makes sense: when the WHERE is evaluated, the groups do not exist yet, so there is nothing to count.

That is the usual case, and each does its own job:

Practical efficiency rule: if a condition can be expressed in WHERE, put it in WHERE. Filtering before grouping means grouping fewer rows. Putting a condition that uses no aggregates into HAVING (HAVING m.branch_id = 2) works in many managers, but it is slower and more confusing.

Now everything falls into place. An SQL query is not evaluated in the order in which it is written. The logical order is this:

This diagram explains at a glance four behaviors that until now looked like whims:

1. WHERE cannot use aggregates. It runs at step 3; the groups are formed at step 4.
2. WHERE cannot use the SELECT aliases. The SELECT is step 6, which comes later. WHERE loan_count >= 2 raises a nonexistent-column error.
3. ORDER BY can use the SELECT aliases. It is step 8, after step 6. That is why ORDER BY loan_count DESC worked.
4. LIMIT comes last. It trims the final result, not the rows read: LIMIT 3 in a query with GROUP BY returns three groups, not three rows of the table.

We insist on the word logical: it is the order in which you have to reason about the query. The optimizer (lesson 01-04) is free to execute things in a different physical order as long as the result is the same.

This is where the last two lessons come together and BiblioRed starts producing real reports.

Note the nuance: a loan's branch is the branch of the copy lent, which need not be the member's home branch.

The North branch concentrates half the activity. They add up to 12 ✓.

Important: two books are missing from this listing. "Urban Gardening Handbook" and "Ensanche Records (1904)" have never been lent, and the INNER JOIN removes them before grouping. If the report has to include them with a 0, you have to combine LEFT JOIN with COUNT(column):

There is a lesson here that is worth the price of admission on its own: COUNT(*) would have returned 1 instead of 0 for the last two, because the LEFT JOIN generates a row filled with NULL and COUNT(*) counts rows. COUNT(l.loan_id) counts non-null values, and there are none there.

With a LEFT JOIN, never COUNT(*): always COUNT(column_from_the_right_table).

This is the complete member-activity report, with the inactive ones included. Compare it with the anti-join from the previous lesson: there we only got who had no loans; now we have the whole picture.

We pick up the exercise we left half-finished in lesson 02-04. A BiblioRed manager asks: "how many members and how many copies are there at each branch". The query that writes itself is this one:

Every number is wrong, and the fact that the two columns are identical is the alarm signal. Central has 4 members and 6 copies, not 24 and 24.

The cause: members and copies are not related to each other; both hang off branches independently. When they are joined, a Cartesian product occurs within each branch: 4 members × 6 copies = 24 rows, and each COUNT counts those 24. It is the row explosion (fan trap).

The three solutions, from worst to best:

Correct. But COUNT(DISTINCT) is expensive and it only rescues the counts: a SUM would still be inflated, because it would add the same value several times. If instead of counting copies you were adding up amounts, the result would be false and DISTINCT would not fix it.

All three return the correct table. Option c) is the one to internalize: when two independent branches have to be summarized, aggregate each one on its own and join the summaries. It is the only one that scales to any number of branches and to any aggregate, not just COUNT.

How to spot the problem in practice: if a total looks suspiciously large, strip out the aggregates and run the query with SELECT *. If far more rows come out than you expected, you have an explosion.

COALESCE(a, b, c, ...) returns the first argument that is not NULL. It is the tool for explicitly deciding what to do with the absence of a value.

Without the COALESCE, four members would show NULL instead of 0.00: the two with no loans at all (Etxebarri and Roig) and the two whose only loan is still open and therefore has no surcharge computed (Salom and Miralles). A report with NULL in a money column is a report nobody knows how to read.

Notice the order in which it is applied: COALESCE wraps the aggregate, not the other way round. SUM(COALESCE(l.surcharge, 0)) would also work and would even be semantically more precise (it treats each individual NULL as zero), but for the total it makes no difference… except with AVG, where it does change the result, because it alters the number of values averaged. Check it:

CASE is SQL's conditional structure:

Placed inside a SUM or a COUNT, it lets you cross-tabulate: produce several columns that count different things about the same group. It is how you build a dashboard in a single query.

This is exactly the complete grid that GROUP BY branch_id, status could not give: here the zeros do appear, because the columns are fixed by the query and do not depend on the data.

How it works: for each row, the CASE produces 1 or 0, and SUM adds them up. A widely used alternative is COUNT(CASE WHEN condition THEN 1 END) —with no ELSE, so the rows that do not match give NULL and COUNT ignores them—.

PostgreSQL also offers a more elegant standard syntax, the FILTER clause:

Same result, far more readable. FILTER does not exist in SQLite or MySQL; there you need CASE WHEN. And CASE WHEN works everywhere, so it is the safe option.

We finish with a capability that solves a problem GROUP BY cannot: computing an aggregate without losing the detail.

Notice the limitation. This query tells you how many loans each member has, but it loses the individual loans:

And what if you want to see each loan and, next to it, how many that member has in total? That is where window functions come in: they compute an aggregate over a set of related rows, but return one row for each original row.

Twelve rows, one per loan, each with its member's total repeated alongside. That is impossible with GROUP BY.

PARTITION BY is to windows what GROUP BY is to aggregates: it defines the subsets. If you leave it out (OVER ()), the window is the whole table.

"Loan number N of each member". This pattern —numbering within each group and keeping loan_number = 1— is the standard way to obtain "the most recent record of each X", a query that without windows is surprisingly awkward.

RANK is like ROW_NUMBER but it ties:

The three functions differ precisely on ties:

• ROW_NUMBER never ties: it numbers 1, 2, 3, 4, 5, 6, 7 even when the values are equal.
• RANK ties and skips: the five books with one loan are all "place 3"; if there were a sixth distinct value, it would be place 8.
• DENSE_RANK ties and does not skip: the next distinct value would be place 4.

We stop here. Window functions have a great deal more to offer —moving averages, running totals, LAG and LEAD to compare with the previous row, window frames with ROWS BETWEEN— and they are the bread and butter of data analysis. They are available in PostgreSQL since version 8.4 and in SQLite since 3.25.

• Leaving a column out of the GROUP BY. In PostgreSQL it raises an error; in SQLite it runs and returns an arbitrary value, which is infinitely worse. Always write the complete GROUP BY.
• Using COUNT(*) with a LEFT JOIN. It returns 1 where it should return 0, because it counts the NULL-filled row. Use COUNT(column_from_the_right_table).
• Counting over a JOIN that inflates rows. If two independent tables hang off a third, each COUNT counts the Cartesian product. Aggregate each branch separately.
• Putting a condition with an aggregate in WHERE. Guaranteed error: aggregates go in HAVING.
• Putting a condition that uses no aggregates in HAVING. It works, but it filters later than necessary and confuses whoever reads it. It goes in WHERE.
• Using a SELECT alias inside the WHERE. The SELECT is evaluated afterwards. In ORDER BY you can.
• Assuming that SUM over pure NULLs gives zero. It gives NULL. Wrap it in COALESCE(SUM(x), 0) if the report needs a number.
• Comparing AVG without deciding what to do with the NULLs. AVG(x) and AVG(COALESCE(x, 0)) give different figures and both can be correct: the question is what the absence means.
• Believing that MIN(a) and MAX(b) come from the same row. They do not. Each aggregate is computed on its own.
• Tip: when an aggregate gives you a number that does not add up, remove the functions and run the query in detail mode with SELECT *. Counting the rows by eye reveals explosions instantly.
• Tip: always write the GROUP BY starting with the primary key of the table you are summarizing (GROUP BY b.book_id, b.title, not just GROUP BY b.title). If there were two books with the same title, grouping by the title would merge them into one.

1. How many members are there at each branch? Show the branch name, including the branches that have none.
2. How many books are there per language?
3. How many copies does each book have? Include the title and sort from most to fewest.
4. What is the total surcharge collected at each branch (by the branch of the copy lent)? Show 0.00 where there has been none.

1. Which authors have more than one work in the collection?
2. Which books have copies at three or more different branches?
3. Which members have returned a book late, and how many times? Sort from most to fewest.
4. Which branches have three or more available copies?

1. Build the loans-per-month table: year-month, number of loans and total surcharge. Hint: in PostgreSQL, TO_CHAR(loan_date, 'YYYY-MM'); in SQLite, strftime('%Y-%m', loan_date).
2. For each member, show their name, the number of loans, the number of reservations and the accumulated surcharge, in a single query and with the correct figures. Watch out for the row explosion.
3. Using window functions, show each loan with: the member, the date, the surcharge, and the running total surcharge for that member.

Solution 1

Total: €7.00, which matches the global SUM(surcharge) from section 1. Here there is no row explosion, because the three tables are chained in a line (branch → copy → loan), not hanging in parallel.

Solution 2

The DISTINCT is essential: if a book had two copies at the same branch, COUNT(c.branch_id) would count them twice.

Note: the condition goes in WHERE because it filters rows (loans), not groups. That is the efficient way to filter.

Solution 3

The four July loans are open, so their surcharge is NULL and SUM returns NULL; the COALESCE turns it into 0.00. And notice that June does not appear: there were no loans that month and GROUP BY does not invent empty groups. To make it show up with a 0 you would have to generate the series of months and LEFT JOIN against it.

If you had solved it with two direct LEFT JOINs to loans and reservations, Marta Alsina would have come out with 3 loans and 3 reservations (3 × 1 = 3 rows), and Nuria Bastos with 2 and 2. The row explosion in its purest form.

By adding ORDER BY inside the OVER, SUM stops being a total and becomes a running total: each row includes all the previous ones in its partition. It is the mechanism behind any cumulative-evolution chart.

With this lesson BiblioRed can now answer not only "what is there", but "how much is there":

• The aggregate functions COUNT, SUM, AVG, MIN and MAX condense many rows into one value. Without GROUP BY, the whole table is a single group.
• COUNT has three forms: COUNT(*) counts rows, COUNT(column) counts non-null values and COUNT(DISTINCT column) counts distinct values.
• Every aggregate ignores NULLs except COUNT(*), and SUM over pure nulls returns NULL, not zero. Deciding what the absence means is a business decision, not a technical one.
• GROUP BY splits the result into groups and returns one row per group; every non-aggregate column in the SELECT must be in the GROUP BY (and SQLite does not check it, which is a serious trap).
• HAVING filters groups; WHERE filters rows. Whatever can go in WHERE, goes in WHERE.
• The logical order of execution —FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT— explains why WHERE sees neither the aliases nor the aggregates and ORDER BY does.
• Aggregation combined with JOIN produces the real reports: loans per branch, most borrowed books, activity per member. With one golden rule: with LEFT JOIN, COUNT(column), never COUNT(*).
• The row explosion inflates counts when two independent tables hang off a third. It is spotted because the figures come out suspiciously high and identical, and it is solved by aggregating each branch separately with CTEs.
• COALESCE turns NULL into something presentable and CASE WHEN (or FILTER in PostgreSQL) lets you cross-tabulate several conditional columns in a single pass.
• Window functions with OVER (PARTITION BY ...) compute aggregates without losing the detail, and ROW_NUMBER, RANK and DENSE_RANK number and rank with different treatments of ties.

We now know how to define the schema, populate it, query it, cross it and summarize it. What remains is the question that holds up everything else: who guarantees that this data will still be true in five years' time? Our reports are reliable only because every member_id in loans points at a member who exists, and every book_id in copies, at a book in the catalog.

That is guaranteed by referential integrity, and it is the subject of lesson 02-06, which closes the module: how foreign keys are declared, what the manager checks on every INSERT, UPDATE and DELETE, what to do when you delete a row that others depend on (CASCADE, RESTRICT, SET NULL…), how to detect and clean up the orphan rows that already exist, and why SQLite protects nothing unless you expressly ask it to.