# Aggregation > Summarise data with COUNT, SUM, AVG and friends, group it with GROUP BY, filter groups with HAVING and avoid double counting. SQL & Databases roadmap · Querying · 17 min read · https://levelcode.org/roadmaps/sql/aggregation So far every query has returned one result row per source row (or per joined pair). Business questions are usually about *summaries*: how many orders came in last month, what each customer has spent, which genre earns the most. Aggregate functions collapse many rows into one value, and `GROUP BY` decides which rows get collapsed together. This lesson covers both, plus `HAVING`, the `FILTER` clause, and the subtle ways `NULL`s and joins distort totals. ## Aggregate functions An aggregate function takes a whole set of rows and returns a single value. Without `GROUP BY`, the set is every row that passed the `WHERE` clause: ```sql SELECT min(price) AS cheapest, max(price) AS priciest, round(avg(price), 2) AS avg_price, sum(stock) AS copies FROM books; ``` ```text cheapest | priciest | avg_price | copies ----------+----------+-----------+-------- 10.99 | 18.50 | 14.07 | 78 (1 row) ``` The core aggregates: | Function | Returns | Ignores NULL? | | --- | --- | --- | | `count(*)` | number of rows | counts every row | | `count(expr)` | number of rows where `expr` is not `NULL` | yes | | `count(DISTINCT expr)` | number of distinct non-`NULL` values | yes | | `sum(expr)` | total | yes | | `avg(expr)` | arithmetic mean | yes | | `min(expr)`, `max(expr)` | smallest / largest | yes | | `string_agg(expr, sep)` | values joined into one string | yes | | `array_agg(expr)` | values collected into an array | no — keeps `NULL`s | ### The three kinds of count The difference between the `count` variants is the first thing to get right: ```sql SELECT count(*) AS customers, count(city) AS with_city, count(DISTINCT city) AS cities FROM customers; ``` ```text customers | with_city | cities -----------+-----------+-------- 6 | 4 | 2 (1 row) ``` Six customers, four of whom have a city, spread across two distinct cities (Leeds and London). `count(city)` skips the two `NULL`s. ### NULLs and empty sets Because aggregates skip `NULL`s, `avg` divides by the number of *non-null* values, which may not be what you mean: ```sql SELECT avg(x) AS avg_ignoring_null, avg(coalesce(x, 0)) AS avg_null_as_zero FROM (VALUES (10), (20), (NULL)) AS t (x); ``` ```text avg_ignoring_null | avg_null_as_zero ---------------------+--------------------- 15.0000000000000000 | 10.0000000000000000 (1 row) ``` Neither is wrong — "average rating among people who rated" versus "average with no rating counted as zero" are different questions. Decide which one you are asking. When *no* rows match, `count` returns `0` but every other aggregate returns `NULL`: ```sql SELECT count(*) AS n, sum(price) AS total, max(price) AS priciest FROM books WHERE genre = 'horror'; ``` ```text n | total | priciest ---+--------+---------- 0 | (null) | (null) (1 row) ``` If your application expects a number, write `coalesce(sum(price), 0)`. ## GROUP BY: one result row per group `GROUP BY` splits the rows into groups that share the same value of the grouping expressions, then computes the aggregates once per group: ```sql SELECT genre, count(*) AS books, round(avg(price), 2) AS avg_price, sum(stock) AS copies FROM books GROUP BY genre ORDER BY genre; ``` ```text genre | books | avg_price | copies ----------+-------+-----------+-------- fantasy | 4 | 11.62 | 48 literary | 2 | 17.75 | 8 sci-fi | 4 | 14.69 | 22 (3 rows) ``` Picture it as sorting the ten books into three piles by genre, then measuring each pile. You can group by several columns (`GROUP BY genre, author_id` gives one row per combination) or by an expression, such as a month: ```sql SELECT date_trunc('month', o.ordered_on)::date AS month, count(DISTINCT o.order_id) AS orders, sum(oi.quantity * oi.unit_price) AS revenue FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.order_id WHERE o.status <> 'cancelled' GROUP BY month ORDER BY month; ``` ```text month | orders | revenue ------------+--------+--------- 2025-01-01 | 2 | 49.97 2025-02-01 | 2 | 67.74 2025-03-01 | 5 | 141.95 (3 rows) ``` `date_trunc('month', ...)` rounds a date down to the first of its month. PostgreSQL lets `GROUP BY` refer to a select-list alias like `month` as a convenience; the SQL standard does not, so some databases require you to repeat the expression. ### The grouping rule Once you group, each result row stands for a whole group, so every item in the select list must have a single value per group. It must either be a grouping expression or be inside an aggregate: ```sql SELECT genre, title, count(*) FROM books GROUP BY genre; ``` ```text ERROR: column "books.title" must appear in the GROUP BY clause or be used in an aggregate function ``` Which of the four fantasy titles would PostgreSQL show? There is no answer, so it refuses. (MySQL in some modes picks one arbitrarily, which hides bugs.) There is one useful exception. If you group by a table's **primary key**, PostgreSQL knows every other column of that table has one value per group, so you may select them freely: ```sql SELECT c.customer_id, c.name, sum(oi.quantity * oi.unit_price) AS revenue FROM customers AS c JOIN orders AS o ON o.customer_id = c.customer_id JOIN order_items AS oi ON oi.order_id = o.order_id WHERE o.status <> 'cancelled' GROUP BY c.customer_id ORDER BY revenue DESC; ``` ```text customer_id | name | revenue -------------+-------------+--------- 1 | Ada Park | 74.96 2 | Ben Okafor | 73.48 4 | Dev Raman | 72.25 5 | Elena Rossi | 38.97 (4 rows) ``` > [!TIP] > Group by the key, not by the name. Two customers can share a name; grouping by `c.name` alone would merge them into one row and add up their spending. ## HAVING: filtering groups `WHERE` filters *rows* before grouping, so it cannot use aggregates. `HAVING` filters *groups* after aggregation. Customers with at least two orders: ```sql SELECT c.name, count(*) AS orders FROM customers AS c JOIN orders AS o ON o.customer_id = c.customer_id GROUP BY c.customer_id HAVING count(*) >= 2 ORDER BY c.name; ``` ```text name | orders ------------+-------- Ada Park | 3 Ben Okafor | 3 Dev Raman | 2 (3 rows) ``` The complete logical evaluation order of a query is now: ```text FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT ``` Put a condition in `WHERE` whenever it is about individual rows (`status <> 'cancelled'`), and in `HAVING` only when it needs an aggregate (`sum(...) > 50`). Filtering early means fewer rows to group. ## Conditional aggregates with FILTER Often you want several differently filtered counts side by side. The `FILTER` clause attaches a condition to one aggregate: ```sql SELECT count(*) AS all_orders, count(*) FILTER (WHERE status = 'paid') AS paid, count(*) FILTER (WHERE status = 'shipped') AS shipped, count(*) FILTER (WHERE status = 'cancelled') AS cancelled FROM orders; ``` ```text all_orders | paid | shipped | cancelled ------------+------+---------+----------- 10 | 4 | 5 | 1 (1 row) ``` This is clearer than the portable alternative, `sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)`, which you will see in older code and other databases. For yes/no questions about a group, `bool_or` is true if the condition holds for any row and `bool_and` only if it holds for every row: ```sql SELECT genre, count(*) AS books, bool_or(stock = 0) AS any_sold_out, bool_and(published < 2000) AS all_pre_2000 FROM books GROUP BY genre ORDER BY genre; ``` ```text genre | books | any_sold_out | all_pre_2000 ----------+-------+--------------+-------------- fantasy | 4 | f | f literary | 2 | t | f sci-fi | 4 | t | t (3 rows) ``` Incidentally, a `GROUP BY` with no aggregates, such as `SELECT genre FROM books GROUP BY genre`, returns the same rows as `SELECT DISTINCT genre FROM books`. Use `DISTINCT` when you only want unique rows and `GROUP BY` when you want to compute something per group; mixing them in one query is usually a sign the grouping is wrong. ## Collecting values and subtotals `string_agg` and `array_agg` gather a group's values, and accept their own `ORDER BY`: ```sql SELECT a.name, string_agg(b.title, ', ' ORDER BY b.published) AS titles FROM authors AS a JOIN books AS b ON b.author_id = a.author_id GROUP BY a.author_id ORDER BY a.name; ``` ```text name | titles -------------------+------------------------------------------------------------------- Haruki Murakami | Norwegian Wood, Kafka on the Shore Octavia E. Butler | Kindred, Parable of the Sower Terry Pratchett | Guards! Guards!, Small Gods, Going Postal Ursula K. Le Guin | A Wizard of Earthsea, The Left Hand of Darkness, The Dispossessed (4 rows) ``` For reports with subtotals, `GROUP BY ROLLUP (genre)` adds a grand-total row in which the rolled-up column is `NULL`: ```sql SELECT genre, count(*) AS books, sum(stock) AS copies FROM books GROUP BY ROLLUP (genre) ORDER BY genre; ``` ```text genre | books | copies ----------+-------+-------- fantasy | 4 | 48 literary | 2 | 8 sci-fi | 4 | 22 (null) | 10 | 78 (4 rows) ``` `ROLLUP (genre, author_id)` produces per-author rows, per-genre subtotals and a grand total in one pass. `GROUPING SETS` and `CUBE` offer finer control over which combinations appear. ## Pitfalls: joins that inflate counts ### Counting the wrong rows after a join A join multiplies the "one" side once per matching row on the "many" side. Count orders per customer *after* joining to `order_items`, and `count(*)` counts order lines instead: ```sql SELECT c.name, count(*) AS rows_counted, count(DISTINCT o.order_id) AS orders FROM customers AS c JOIN orders AS o ON o.customer_id = c.customer_id JOIN order_items AS oi ON oi.order_id = o.order_id GROUP BY c.customer_id ORDER BY c.name; ``` ```text name | rows_counted | orders --------------+--------------+-------- Ada Park | 5 | 3 Ben Okafor | 5 | 3 Chloe Martin | 1 | 1 Dev Raman | 4 | 2 Elena Rossi | 1 | 1 (5 rows) ``` `count(DISTINCT ...)` fixes counts, but sums are harder: if orders had a `shipping_fee` column, `sum(o.shipping_fee)` here would add each fee once per line. The cure is to aggregate at the right level first — for example, total each order's lines in a subquery, then join that to customers — a technique you will learn in the next lesson. ### count(*) with a LEFT JOIN To include customers with no orders you need a `LEFT JOIN`, but `count(*)` counts the `NULL`-padded row as one: ```sql SELECT c.name, count(*) AS count_star, count(o.order_id) AS orders FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id GROUP BY c.customer_id ORDER BY c.name; ``` ```text name | count_star | orders --------------+------------+-------- Ada Park | 3 | 3 Ben Okafor | 3 | 3 Chloe Martin | 1 | 1 Dev Raman | 2 | 2 Elena Rossi | 1 | 1 Farid Haddad | 1 | 0 (6 rows) ``` > [!WARNING] > After a `LEFT JOIN`, count a column from the right-hand table — ideally its key — not `*`. `count(o.order_id)` correctly reports zero for Farid, because the padded row's `order_id` is `NULL`. ## Practice 1. For each genre, show the number of copies sold and the revenue from non-cancelled orders. Only show genres with more than £50 of revenue, highest revenue first. 2. For **every** customer, show the number of non-cancelled orders and their total spend. Customers with no such orders must show `0` for both. Sort by spend (highest first), then name. **Solution** ```sql SELECT b.genre, sum(oi.quantity) AS copies_sold, sum(oi.quantity * oi.unit_price) AS revenue FROM books AS b JOIN order_items AS oi ON oi.book_id = b.book_id JOIN orders AS o ON o.order_id = oi.order_id WHERE o.status <> 'cancelled' GROUP BY b.genre HAVING sum(oi.quantity * oi.unit_price) > 50 ORDER BY revenue DESC; ``` ```text genre | copies_sold | revenue ---------+-------------+--------- fantasy | 12 | 139.41 sci-fi | 6 | 86.25 (2 rows) ``` `literary` earned 34.00 and is removed by `HAVING`. The status test is a row condition, so it goes in `WHERE`. ```sql SELECT c.name, count(DISTINCT o.order_id) AS orders, coalesce(sum(oi.quantity * oi.unit_price), 0) AS spent FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id AND o.status <> 'cancelled' LEFT JOIN order_items AS oi ON oi.order_id = o.order_id GROUP BY c.customer_id ORDER BY spent DESC, c.name; ``` ```text name | orders | spent --------------+--------+------- Ada Park | 3 | 74.96 Ben Okafor | 3 | 73.48 Dev Raman | 2 | 72.25 Elena Rossi | 1 | 38.97 Chloe Martin | 0 | 0 Farid Haddad | 0 | 0 (6 rows) ``` Three details make this correct: the status filter lives in `ON` so Chloe is not dropped; `count(DISTINCT o.order_id)` counts orders rather than lines and ignores the padded `NULL`; and `coalesce` turns the `NULL` sum into `0`. ## Key takeaways - Aggregates collapse many rows into one value; all except `count(*)` ignore `NULL`s, and all except `count` return `NULL` over zero rows. - `GROUP BY` produces one row per group; every selected column must be grouped or aggregated, though grouping by a primary key lets you select that table's other columns. - `WHERE` filters rows before grouping; `HAVING` filters groups after aggregation. - `FILTER (WHERE ...)` computes several conditional aggregates in one pass. - Joins multiply rows: use `count(DISTINCT key)`, count a right-hand key after a `LEFT JOIN`, and aggregate at the correct level before summing. ## Quiz 1. customers has 6 rows; 2 of them have city = NULL. What does SELECT count(city) FROM customers; return? A. 4 B. 6 C. 2 D. NULL **Answer: A.** count(expr) counts only rows where the expression is not NULL. count(*) would return 6. 2. You want only authors with more than two books. Where does count(*) > 2 go? A. In WHERE B. In the ON clause of the join C. In HAVING D. In the SELECT list **Answer: C.** WHERE is evaluated before grouping and cannot see aggregate values. HAVING filters groups after aggregation, so HAVING count(*) > 2 is correct. 3. What does SELECT sum(price) FROM books WHERE genre = 'horror'; return when no books are horror? A. 0 B. Zero rows C. An error D. One row containing NULL **Answer: D.** An aggregate query without GROUP BY always returns exactly one row. Over an empty set, sum returns NULL (only count returns 0). Use coalesce(sum(price), 0) if you need a number. 4. customers LEFT JOIN orders ... GROUP BY c.customer_id. For a customer with no orders, what does count(*) return? A. 0 B. 1 C. NULL D. The total number of orders **Answer: B.** The LEFT JOIN produces one NULL-padded row for that customer, and count(*) counts rows, so it returns 1. count(o.order_id) returns the correct 0.