Up to now, every row of your results came from a row of the database. SELECT projected columns, WHERE discarded rows, JOIN combined tables: the granularity could multiply, but you were still working row by row. Aggregate functions break that correspondence. They take in many rows and return a single value: a total, a count, an average, a maximum.

It's the step that turns a query into a report. "How much have we billed?", "how many orders are pending?", "what's the average order value?" are questions no previous query could answer. In this lesson you'll learn the five fundamental functions, you'll see why they all ignore NULLs except COUNT(*) —and why that sometimes suits you and sometimes deceives you—, you'll discover that SUM over an empty set returns NULL while COUNT returns 0, and you'll finally resolve the warning module 3 repeated three times: summing the shipping costs after joining with the detail inflates the total. You'll see the wrong number, the right number and the two correct ways of framing it.

1. What an aggregate function is
2. The five functions and their return types
3. The three forms of COUNT
4. SUM and AVG over the sales detail
5. AVG ignores NULLs: two correct answers to different questions
6. MIN and MAX over numbers, dates and text
7. The empty set: SUM gives NULL, COUNT gives 0
8. An aggregate collapses the whole table, and can't be mixed with a bare column
9. FILTER (WHERE ...): aggregating only part of it
10. STRING_AGG and ARRAY_AGG: non-numeric aggregates
11. Resolving module 3's warning
12. Common Mistakes and Tips
13. Exercises
14. Conclusion

An aggregate function walks a set of rows and produces a single value.

The simplest possible query with an aggregate:

A single row, even though the table has 47. That collapse is the operation's defining feature, and every rule in the lesson comes out of it.

Two observations worth fixing from the start:

• An aggregate with no GROUP BY collapses the whole table into one row. It always returns exactly one, even if the table is empty.
• Aggregate functions can't be used in the WHERE. The WHERE decides which rows go into the set, so it can't depend on a calculation made over that very set. For filtering by an aggregate there's HAVING, and that's lesson 04-06.

And the return types in PostgreSQL, which matter more than they look:

Two details with practical consequences:

1. SUM of integers returns BIGINT, not INTEGER. It's a protection against overflow: adding up a million large integers overflows INTEGER easily.
2. AVG of an integer returns NUMERIC, not an integer. PostgreSQL doesn't truncate. You'll see it in section 6, and it's an important difference from other engines.

COUNT has three spellings that almost never give the same number, and confusing them is one of the most common sources of error in reports.

The best possible demonstration is in orders.employee_id, which has 20 rows, 10 values and 3 distinct sales reps:

20, 10 and 3. Three numbers over the same column of the same table, and all three are correct because they answer different questions:

• 20: how many orders are there? Every order exists, with or without a sales rep.
• 10: how many orders were handled by a sales rep? The ten from the phone channel. The web channel's NULLs aren't counted.
• 3: how many sales reps have handled any order? Óscar (4), Laia (5) and Marc (6). The other five employees never appear.

This is the definitive proof that aggregates ignore NULLs: COUNT(*) is the only form that counts the web channel's ten rows, because it's the only one that doesn't look at any value.

That last one is worth seeing, because it connects with 02-04's DISTINCT and with 03-02's disappearing customers:

12 of the 15 customers have bought at some point. The three missing ones are Núria, Hugo and Inés, exactly the ones you recovered with 03-03's anti-join. And notice something important: COUNT(DISTINCT customer_id) over orders can't tell you there are 15 in total, because in orders they don't exist. For that you have to start from customers.

Performance warning: COUNT(DISTINCT column) is noticeably more expensive than COUNT(column), because it forces the engine to sort or to build a hash table with all the values. With 20 rows it's irrelevant; with a hundred million, it's the difference between a second and several minutes. Use it when you need it, not out of habit.

Dialect note: COUNT(DISTINCT a, b) with several columns works in MySQL but not in PostgreSQL, where you have to write COUNT(DISTINCT (a, b)) using row-constructor syntax. And COUNT(*) versus COUNT(1): in PostgreSQL they're identical in performance and both are optimised the same way, so the choice is purely stylistic. The course uses COUNT(*).

Now the question the business really asks: how much have we billed? A line's amount is the usual one, and it's calculated with ol.unit_price (03-02's rule), never with p.price.

These are GreenStore's master figures: 47 lines, 113 units sold and €727.95 of product revenue (not counting shipping costs, which we'll see in section 11).

This is the moment to check with numbers what 03-02 announced. Lines 1 and 4 carry the historical price, from before the April 2025 price rise:

2 lines out of 47. The accumulated difference is €2.50, small change in this data set. But look at what it means: if you aggregated over p.price, you'd be stating that in March 2025 you billed €2.50 that never came through the till. With a real catalogue and several price rises a year, that figure runs into the thousands.

The course's rule, now with aggregates: SUM of sales always over ol.unit_price. products.price is for answering "how much does it cost today?", never "how much did we bill back then?".

Aggregates combine with WHERE perfectly naturally, and the WHERE acts first:

€124.43 across the four orders of 2026, against €603.52 across the sixteen of 2025. They add up to the €727.95 total, as they should.

That AVG ignores nulls looks like a technical detail. It's a business decision in disguise, and it's worth seeing with a case where the difference hurts.

The question: "what's the average salary of the sales rep who handles our orders?"

AVG has returned €27,420: it added up €274,200 and divided by 10, not by 20, because the ten web orders have e.salary at NULL and AVG ignores them.

Now the other reading of the same question:

€13,710, exactly half. And both figures are correct:

The second makes sense if you're spreading the sales cost across the whole order volume, including the ones that consume nobody's time. The first, if you're comparing sales-rep profiles. Choosing wrong doesn't raise an error: it gives a figure that doubles or halves reality.

The explicit way of treating nulls as zeros is AVG(COALESCE(e.salary, 0)), which would return the same €13,710. COALESCE is studied in 06-04; mention it to yourself every time you write an AVG over a nullable column.

The question to always ask yourself before writing AVG: "is the average over the rows that have data, or over all the rows?". If you can't answer it, the query isn't defined yet.

MIN and MAX work over any type that can be ordered, not just numbers. It's their most underused feature.

Four data types in one query: NUMERIC, DATE and TEXT. Over text, MIN/MAX use the database's collation (02-05), so the result can vary between differently configured servers.

The most frequent use in practice is over dates, to bound the history:

Eleven and a half months of history, from 4 March 2025 to 21 February 2026, with shipping between €0.00 (free delivery) and €12.50.

The worst rating in all of GreenStore is a 2, the ginger kombucha's ("far too much ginger for my taste"). And look at the average: sixteen decimals. It happens because AVG over a SMALLINT column returns NUMERIC, and dividing two NUMERICs in PostgreSQL is computed with at least 16 significant digits so as not to lose precision. It isn't a bug: it's the guarantee that the engine hasn't rounded on its own initiative.

The way to present it is the usual one: compute at full precision and round only when displaying.

Dialect note: this is one of the points where engines diverge the most, and where the most silent errors happen when porting code.

Engine	AVG over an integer column	Result with 49/12
PostgreSQL	NUMERIC at full precision	4.0833333333333333
MySQL	DECIMAL	4.0833
SQLite	Always REAL (floating point)	4.083333333333333
SQL Server	INT: it truncates	4
Oracle	NUMBER	4.08333333333333…

SQL Server is the dangerous case: AVG of an INT column does integer division and returns 4. To get the decimal you have to cast explicitly: AVG(CAST(rating AS DECIMAL(10,2))). A report ported from PostgreSQL to SQL Server can start rounding down with nobody noticing.

This is a classic reporting trap, and GreenStore has the perfect case: category 6 (Supplements) has a single product, number 20 (Spirulina capsules), which is discontinued and has never been sold.

A single row, with one 0 and four nulls. Two things to learn from this:

1. A query with aggregates and no GROUP BY always returns one row, even if the WHERE lets none through. It doesn't return "0 rows": it returns one row with the result of aggregating nothing.
2. COUNT of nothing is 0; SUM, AVG, MIN and MAX of nothing are NULL. It's consistent: counting zero elements gives zero, but the sum of an empty set has no natural value to return, and its average even less so.

Why it matters: if your report computes revenue * 1.21 to add the VAT, that cell will show NULL, not 0.00. And if the application consuming the result expects a number, it may fail. The solution is COALESCE(SUM(...), 0), which you'll see in 06-04.

Notice the right-hand column: it's the same behaviour. For SUM and AVG it makes no difference whether there are no rows at all or there are rows with every value null. It's consistent with section 3's rule: aggregates discard nulls before operating, so "all null" and "empty" end up being the same set.

You've already seen it: with no GROUP BY, the aggregate applies to all the rows coming out of the WHERE and produces one row. And from that comes the most important restriction of this lesson.

The error is entirely right. Think about it: COUNT(*) is going to return a single row with the value 20. What should appear in that row's name column? "Extra virgin olive oil"? "Cold-pressed orange juice"? All twenty concatenated? The question has no answer, and PostgreSQL refuses to invent one.

The general rule, which will govern the whole of lesson 04-05:

Every column in the SELECT must be inside an aggregate function or be part of the GROUP BY. There's no third option.

There are three ways out here, and each answers a different question:

This is exactly the doorway into GROUP BY. Most business questions aren't "how much have we billed?" but "how much have we billed per category?", and that requires splitting the table into groups before aggregating.

Dialect note: MySQL with the ONLY_FULL_GROUP_BY mode switched off accepts that query without protest and returns an arbitrary value of name. SQLite always does the same. It's a convenience that produces silently incorrect reports, and lesson 04-05 covers it in detail with a comparison table. Since MySQL 5.7.5 the mode is on by default, precisely because of this.

You often want several aggregates over different subsets in the same result row: total, delivered, cancelled. Writing three queries and stitching them together is tedious. PostgreSQL offers the FILTER clause:

Six metrics in a single pass over the table. The syntax is AGGREGATE(expr) FILTER (WHERE condition): the condition decides which rows go into that particular aggregate, without affecting the others or the query's general WHERE.

An example with money, comparing GreenStore's two trading years:

Only 6 of the 47 lines carry a discount. (EXTRACT is covered in depth in 06-03; here it just extracts the year. The subquery in the FROM is module 7: here it serves only to avoid repeating the amount expression three times.)

Portability: FILTER is standard SQL but only PostgreSQL (9.4+) and SQLite (3.30+) implement it. In MySQL, SQL Server and Oracle you have to write the classic, portable form, which consists of putting a CASE inside the aggregate:

COUNT(CASE WHEN status = 'delivered' THEN 1 END)  AS delivered
SUM(CASE WHEN status = 'delivered' THEN shipping_cost ELSE 0 END) AS shipping_delivered

It works because COUNT ignores the NULLs a CASE without ELSE returns. It's exactly section 3's mechanism, exploited on purpose. CASE is lesson 06-05.

Not every aggregate produces numbers. Two PostgreSQL ones are especially useful and worth knowing now, even though their natural ground is tomorrow's GROUP BY:

• STRING_AGG(expression, separator) concatenates the values of every row into a single string. The internal ORDER BY is essential: without it, the concatenation order is arbitrary and the result isn't reproducible.
• ARRAY_AGG(expression) does the same but returns a PostgreSQL array, useful when the application is going to process the values separately.

Both ignore NULLs, like the rest.

Dialect note: STRING_AGG exists in PostgreSQL and in SQL Server (2017+). MySQL and SQLite have GROUP_CONCAT with a different syntax, and Oracle uses LISTAGG. ARRAY_AGG is PostgreSQL-specific, because it depends on the engine having native array types.

Now for the main course. Module 3 warned you three times —in 03-02, in 03-03 and in its conclusion— that summing a header value after joining with the detail inflates the result. Now you have the tools to see it, measure it and fix it.

The question: how much have we taken in total from shipping costs?

€278.70 against €118.25. The error is €160.45: the inflated figure is 2.36 times the real one, a factor very close to the average number of lines per order (47 / 20 = 2.35). It doesn't match exactly because the orders with the most lines aren't the ones paying the most shipping, but the order of magnitude of the error is always set by that ratio.

The €4.95 GreenStore charged once appear on three rows, because the order has three lines. SUM doesn't know they're the same charge repeated: it adds up what it sees, €14.85 for this order. And there's no error, no warning: just a wrong number in a management report.

Way 1 (today's): aggregate each thing at its own level of granularity.

If the question is about headers, aggregate the header table. If it's about lines, aggregate the lines. Two separate queries, each at its natural granularity:

Total billed by GreenStore: 727.95 + 118.25 = €846.20. This is the correct figure, and you get it by adding two aggregates computed separately, each over its own table.

Way 2 (module 7's): aggregate the detail first and join it afterwards.

When you need both figures in the same query —for instance, each order's total with its shipping— the correct technique consists of collapsing the detail to order level before joining it with the header. That requires a subquery or a CTE, which are modules 7 and 10. Here's the skeleton, so you recognise it when it arrives:

The key idea: the subquery reduces the 47 lines to 20 rows, one per order. When you join it with orders there's no multiplication any more, and o.shipping_cost appears once per order. It's the general solution to the problem, and that's why 07-01 starts right here.

That last check, applied here:

47 ≠ 20: there's multiplication. As soon as you see that inequality, you know you can't sum any column from orders without correcting it.

• Summing a header column after joining with the detail. This lesson's mistake: €278.70 instead of €118.25. Aggregate each table at its own level.
• Confusing COUNT(*), COUNT(column) and COUNT(DISTINCT column). 20, 10 and 3 over the same column. Choose by the question, not out of habit.
• Using COUNT(column) believing it counts rows. It counts non-null values. If the column allows nulls, you're missing rows.
• Forgetting that AVG ignores nulls. €27,420 against €13,710. Always ask yourself whether the average is over the rows with data or over all of them.
• Expecting 0 from a SUM with no rows. It returns NULL. COALESCE(SUM(...), 0) in 06-04.
• Putting an aggregate in the WHERE. ERROR: aggregate functions are not allowed in WHERE. It's HAVING (04-06).
• Mixing a bare column with an aggregate. column ... must appear in the GROUP BY clause. That's GROUP BY (04-05).
• Calculating the amount with p.price instead of ol.unit_price. It rewrites commercial history: €2.50 too much in GreenStore, thousands in a real catalogue.
• Rounding before aggregating. SUM(ROUND(x, 2)) accumulates each rounding's error. Compute at full precision and round the final result.
• Assuming AVG of an integer returns decimals on every engine. SQL Server truncates. Cast explicitly if the code has to travel.
• Tip: write the query without aggregating first and count the rows. If the count isn't what you expect, whatever aggregate you put on top will be wrong even if the syntax is perfect.
• Tip: validate every new figure against one you already know. €727.95 of revenue must reconcile with €603.52 from 2025 plus €124.43 from 2026. If it doesn't, there are rows too many or too few.
• Tip: use FILTER to gather several metrics in a single pass. It's faster than firing off five queries and more readable than five nested CASEs.

Management asks for a single-row dashboard with these six metrics over orders:

1. Total number of orders.
2. Number of distinct customers who have bought.
3. Number of delivered orders.
4. Total income from shipping costs.
5. Date of the first and the last order.
6. Number of web-channel orders (with no sales rep).

Write a single query. Then answer: why can't point 2 give you the 15 customers in the customers table?

Over reviews, calculate the number of reviews, the average rating rounded to two decimals, the worst and the best rating, and how many distinct products have been reviewed.

Then answer these two questions and justify them with numbers:

1. How many catalogue products have no review at all? Can you get it from this same query?
2. If you calculated AVG(rating) over a LEFT JOIN of products with reviews, would the same average come out? Why?

The finance director wants the total billed by GreenStore in 2025, shipping costs included. An intern hands him this:

1. Run the query and say what number it gives.
2. Explain exactly which part is wrong and why.
3. Calculate the correct number with the two separate queries it calls for.
4. State how big the error is, in euros and as a percentage.

Solution 1

Why point 2 gives 12 and not 15: because the query starts from orders, and in orders there's no row at all whose customer_id is 13, 14 or 15. Núria, Hugo and Inés have never bought, so there's nothing to count. COUNT(DISTINCT customer_id) answers "how many customers have bought?", not "how many customers do we have?". For the latter you have to ask customers:

It's the same lesson as 03-02: the table you start from determines which questions you can answer.

Solution 2

12 reviews over 9 distinct products: three products (the oil, the rice and the face cream) have two reviews each.

1. The products with no review are 11: the catalogue's 20 minus the 9 reviewed ones. But you can't get it from this query, because it starts from reviews and the products with no review don't appear there — it's literally 03-02's INNER JOIN problem. You have to ask products with an anti-join, as in solution 1 of 03-03:

9 + 11 = 20. It closes.

2. Yes, exactly the same average would come out: 4.08. And this is a surprising result. With products LEFT JOIN reviews you'd get 23 rows (the 12 reviews plus the 11 products with none), but in those 11 extra rows r.rating is NULL, and AVG ignores nulls. It still adds up to 49 and divides by 12.

23 rows, 12 values, the same average. It's section 5 in its purest form: the LEFT JOIN changed the number of rows but not the set of aggregated values. If what you wanted was "the catalogue's average rating treating products with no review as a 0", you'd have to say so explicitly with COALESCE (06-04) — and it would be a fairly questionable metric.

Solution 3

1. What number it gives:

2. What's wrong. The first SUM is correct: quantity, unit_price and discount live in order_lines, which is exactly the FROM's granularity. It adds up 2025's 40 lines and gives €603.52.

The second SUM is incorrect: shipping_cost lives in orders, and after the JOIN each order appears as many times as it has lines. 2025's 16 orders have become 40 rows, so their shipping has been counted 40 times instead of 16. Instead of €85.95 it gives €219.05.

3. The correct calculation, with two queries at their respective granularities:

Correct 2025 total: 603.52 + 85.95 = €689.47.

4. The error.

€133.10 too much, 19.3 % over the correct total. And notice how dangerous the case is: the inflated figure isn't absurd —it isn't ten million, it's a plausible number nobody would question in a meeting. That's why module 3 insisted three times: this mistake isn't detected by reading the result, it's only detected by understanding the granularity.

You now know how to turn rows into figures:

• An aggregate function takes in many rows and returns a value. With no GROUP BY it collapses the whole table into a single row, even if the WHERE lets none through.
• The five functions: COUNT, SUM, AVG, MIN and MAX. MIN and MAX work over any orderable type, dates and text included.
• The three forms of COUNT give three different numbers over the same column: COUNT(*) = 20 orders, COUNT(employee_id) = 10 with a sales rep, COUNT(DISTINCT employee_id) = 3 sales reps. It's the best proof that aggregates ignore NULLs and that COUNT(*) is the only exception.
• GreenStore's master figures: 47 lines, 113 units, €727.95 of product revenue, €118.25 of shipping, €846.20 total. By year: €603.52 in 2025 and €124.43 in 2026.
• AVG ignores nulls, and that can be what you want or exactly the opposite: €27,420 dividing by 10 values, €13,710 dividing by 20 rows. Both figures are correct for different questions.
• Over the empty set, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL. GreenStore's category 6 proves it.
• AVG returns NUMERIC in PostgreSQL and that's why it shows sixteen decimals; SQL Server, by contrast, truncates the average of an integer column.
• You can't mix a bare column with an aggregate: column ... must appear in the GROUP BY clause. That error is the doorway into the next lesson.
• FILTER (WHERE ...) gathers several metrics in a single pass; its portable equivalent is a CASE inside the aggregate (06-05). STRING_AGG and ARRAY_AGG aggregate text and arrays.
• Module 3's warning is resolved: SUM(o.shipping_cost) after joining with order_lines gives €278.70 instead of €118.25, because each shipping charge is repeated as many times as the order has lines. The two solutions are to aggregate each table at its own level or to collapse the detail before joining (module 7).

In the next lesson, aggregating data with GROUP BY, you'll take the leap that turns all of this into genuine analysis. Instead of one figure for the whole company, you'll get one figure per group: sales by category, orders by status, customers by country, units by product. We'll finally extend the logical execution order diagram with GROUP BY and HAVING between WHERE and SELECT —we promised it in 02-01—, you'll see why NULLs form a group of their own, and you'll discover why an INNER JOIN hides category 6 while a LEFT JOIN with COUNT(column) shows it with an honest 0.