Aggregation and Grouping

Table of Contents

1. From Rows to Answers
2. The Aggregate Functions
3. COUNT(*) vs COUNT(column)
4. GROUP BY: One Result Row Per Group
5. The Golden Rule of GROUP BY
6. WHERE vs HAVING
7. Aggregating Across Joins: Revenue Reports
8. Keeping Customers With Zero
9. Grouping by Month With date_trunc
10. How NULL Behaves in Aggregates
11. string_agg and the FILTER Clause
12. Counting Correctly With DISTINCT
13. Order of Evaluation, Again
14. Try It Yourself

1. From Rows to Answers

So far each query returned individual rows: a customer, an order, a line. But business questions are usually about totals: How many orders did we get? What is our revenue? Which category sells best? Nobody wants to scan thousands of rows and add them in their head.

Aggregation collapses many rows into one value (or one value per group). It is the difference between a list of orders and a sales report, and it is done by the database in one query, far faster than pulling every row into your program and looping.

Save this chapter's queries in shopdb/queries/08_aggregation.sql. Run it with:

docker exec -i shopdb-pg psql -U postgres -d shopdb < queries/08_aggregation.sql

2. The Aggregate Functions

An aggregate function takes a set of values and returns a single value. The five you will use every day:

Without GROUP BY, the aggregate treats the whole table as one group:

SELECT count(*)           AS products,
       sum(price)         AS total_price,
       round(avg(price), 2) AS avg_price,
       min(price)         AS cheapest,
       max(price)         AS priciest
FROM products;

 products | total_price | avg_price | cheapest | priciest
----------+-------------+-----------+----------+----------
        8 |      197.50 |     24.69 |     5.50 |    79.00
(1 row)

Eight products whose prices add up to 197.50, so the average is 197.5 / 8 = 24.6875, which we rounded to 24.69 for display. Always round(avg(...), 2) for money: avg on a numeric column returns a long tail of decimals.

Notice the result is one row, however many products there are. That is what aggregation does.

MIN and MAX also work on text and dates: the alphabetically first product name, or the earliest ordered_at.

3. COUNT(*) vs COUNT(column)

These look similar and give different answers when there are NULLs.

• COUNT(*) counts rows.
• COUNT(city) counts rows where city is not NULL.

SELECT count(*)    AS customers,
       count(city) AS with_city
FROM customers;

 customers | with_city
-----------+-----------
         5 |         4
(1 row)

Maly Ros has no city, so count(city) skips her. This is the standard way to ask "how many rows have this field filled in?"

A third form, COUNT(DISTINCT city), counts the number of different non-null values. Here that is 3 (Phnom Penh, Siem Reap, Battambang), even though four customers have a city.

4. GROUP BY: One Result Row Per Group

The real power comes with GROUP BY. It splits the rows into groups that share the same value, then runs the aggregates once per group.

How many orders are in each status?

SELECT status, count(*) AS orders
FROM orders
GROUP BY status
ORDER BY status;

  status   | orders
-----------+--------
 cancelled |      1
 paid      |      2
 pending   |      1
 shipped   |      1
(4 rows)

Think of it as three steps. PostgreSQL gathers the five orders, sorts them into buckets by status, then counts each bucket. Four distinct statuses, four rows, and the counts add up to the original 5.

You can group by several columns, and by expressions. The same idea applied to customers by city:

SELECT city, count(*) AS customers
FROM customers
GROUP BY city
ORDER BY city;

    city    | customers
------------+-----------
 Battambang |         1
 Phnom Penh |         2
 Siem Reap  |         1
            |         1
(4 rows)

NULL is treated as its own group, so Maly appears as a row with a blank city. By default PostgreSQL sorts NULL last in ascending order.

5. The Golden Rule of GROUP BY

Here is the rule that causes most beginner errors:

Every column in SELECT must either be in the GROUP BY or be inside an aggregate function.

Why? After grouping, each result row stands for many original rows. If you ask for name but group by city, which of the several names in the group should be shown? The database refuses to guess:

SELECT city, name, count(*)
FROM customers
GROUP BY city;

ERROR:  column "customers.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT city, name, count(*)
                     ^

(The exact wording differs slightly between PostgreSQL versions.) Fix it by thinking about what you really want: add name to the GROUP BY (then each group is one customer, which is not a useful summary), or aggregate it with something like min(name) or string_agg.

A convenient special case: if you group by a table's primary key, PostgreSQL knows every other column of that table is determined by it, so GROUP BY c.id lets you select c.name too. Many people still list both (GROUP BY c.id, c.name), which works everywhere and is always safe.

6. WHERE vs HAVING

Both filter, but at different moments:

• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.

You cannot use an aggregate in WHERE because the groups do not exist yet. This fails:

SELECT customer_id, count(*)
FROM orders
WHERE count(*) >= 2        -- wrong
GROUP BY customer_id;

ERROR:  aggregate functions are not allowed in WHERE

The right tool is HAVING. Which customers have placed at least two orders?

SELECT c.name, count(o.id) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING count(o.id) >= 2
ORDER BY c.name;

    name    | orders
------------+--------
 Dara Kim   |      2
 Sokha Chan |      2
(2 rows)

When both are needed, use each for its own job: WHERE to drop rows you never want counted (for example cancelled orders), HAVING to keep only the groups that pass a threshold. Filtering rows with WHERE first is also faster, since there is less to group.

7. Aggregating Across Joins: Revenue Reports

Now combine chapter 7 with this one. A single order's total is the sum of its lines, each line being quantity * unit_price:

SELECT order_id,
       sum(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;

 order_id | total
----------+-------
        1 | 26.00
        2 | 79.00
        3 | 60.50
        4 | 57.50
        5 | 22.00
(5 rows)

Check order 1 by hand: 1 x 15.00 plus 2 x 5.50 is 15.00 + 11.00 = 26.00.

Revenue per customer needs three tables: customers, orders, lines.

SELECT c.name,
       sum(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY revenue DESC;

    name    | revenue
------------+---------
 Sokha Chan |  105.00
 Dara Kim   |   82.50
 Linda Vong |   57.50
(3 rows)

Sokha: 26.00 + 79.00 = 105.00. Dara: 60.50 + 22.00 = 82.50 (this includes her cancelled order, a point we fix below). Linda: 57.50.

Notice we can ORDER BY revenue, the alias, because ORDER BY runs after SELECT. And revenue per category goes through products:

SELECT cat.name AS category,
       sum(oi.quantity * oi.unit_price) AS revenue,
       sum(oi.quantity) AS units_sold
FROM order_items oi
JOIN products p     ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
GROUP BY cat.name
ORDER BY revenue DESC;

  category   | revenue | units_sold
-------------+---------+------------
 Electronics |  135.00 |          6
 Books       |   70.00 |          3
 Home        |   40.00 |          4
(3 rows)

Note we used oi.unit_price (the price at the time of sale) and not p.price (today's price). If a price changes next month, old revenue should not change. That is exactly why order_items stores its own copy, a point chapter 10 returns to.

Cancelled orders should not count as revenue. That is a WHERE condition, since it filters rows before grouping:

SELECT c.name,
       sum(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
ORDER BY revenue DESC;

Dara's revenue drops from 82.50 to 60.50, because her cancelled order (22.00) is gone.

8. Keeping Customers With Zero

The revenue report above lists only 3 of the 5 customers. Vuthy and Maly have no orders, so the inner joins removed them. To list everyone, use LEFT JOIN as you learned last chapter. But then SUM over no rows returns NULL, not zero, so wrap it in COALESCE:

SELECT c.name,
       coalesce(sum(oi.quantity * oi.unit_price), 0) AS revenue
FROM customers c
LEFT JOIN orders o       ON o.customer_id = c.id
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY revenue DESC, c.name;

    name    | revenue
------------+---------
 Sokha Chan |  105.00
 Dara Kim   |   82.50
 Linda Vong |   57.50
 Maly Ros   |    0.00
 Vuthy Sok  |    0.00
(5 rows)

Both joins must be LEFT. If the second were a plain JOIN, rows with no order would be dropped again at that step.

The extra c.name in ORDER BY breaks the tie between the two zero rows, so the output order is stable.

9. Grouping by Month With date_trunc

Reports by month are everywhere. date_trunc('month', ts) rounds a timestamp down to the first moment of its month, giving you one value per month to group on:

SELECT date_trunc('month', o.ordered_at)::date AS month,
       sum(oi.quantity * oi.unit_price)       AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY date_trunc('month', o.ordered_at)
ORDER BY month;

   month    | revenue
------------+---------
 2026-01-01 |  105.00
 2026-02-01 |  118.00
(2 rows)

Check January: orders 1 and 2, 26.00 + 79.00 = 105.00. February without the cancelled order 5: order 3 (60.50) plus order 4 (57.50) is 118.00. The ::date cast just trims the time of day for display. (Including the cancelled order would make February 140.00.)

The same function takes 'day', 'week', 'year' and others. In PostgreSQL you may also write GROUP BY 1 to mean "the first column in the SELECT list", a shortcut that is handy but less readable.

10. How NULL Behaves in Aggregates

Aggregate functions ignore NULL values (except COUNT(*), which counts rows). This is usually what you want, but it can mislead.

SELECT avg(x) AS average
FROM (VALUES (1), (NULL), (3)) AS t(x);

       average
--------------------
 2.0000000000000000
(1 row)

The average is 2, not 4/3, because the NULL row was skipped: (1 + 3) divided by 2 values. If you meant "treat missing as zero", say so explicitly with avg(coalesce(x, 0)), which would give 1.33.

Two more facts to remember:

• SUM of an empty set (no rows, or all NULL) is NULL, not 0. Use coalesce(sum(...), 0).
• COUNT of an empty set is 0, never NULL.

When a column can be null, decide deliberately what the aggregate should do about it.

11. string_agg and the FILTER Clause

Two PostgreSQL features worth knowing early.

string_agg(expr, separator) joins the values of a group into one text value. Which products are in each category?

SELECT cat.name AS category,
       string_agg(p.name, ', ' ORDER BY p.name) AS products
FROM categories cat
JOIN products p ON p.category_id = cat.id
GROUP BY cat.name
ORDER BY cat.name;

  category   |                    products
-------------+------------------------------------------------
 Books       | Clean Code, Notebook Pack, SQL Cookbook
 Electronics | Mechanical Keyboard, USB-C Cable, Wireless Mouse
 Home        | Coffee Mug, Desk Lamp
(3 rows)

The ORDER BY inside the parentheses controls the order within each joined string. (Spacing in your terminal will differ slightly with the widest value in the column.)

FILTER (WHERE ...) attaches a condition to a single aggregate, so you can compute several differently-filtered numbers in one pass. Total and paid-only revenue per month, side by side:

SELECT date_trunc('month', o.ordered_at)::date AS month,
       sum(oi.quantity * oi.unit_price) AS total,
       sum(oi.quantity * oi.unit_price)
         FILTER (WHERE o.status = 'paid') AS paid_only
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY date_trunc('month', o.ordered_at)
ORDER BY month;

   month    | total  | paid_only
------------+--------+-----------
 2026-01-01 | 105.00 |     26.00
 2026-02-01 | 140.00 |     57.50
(2 rows)

January: orders 1 (paid, 26.00) and 2 (shipped, 79.00), so total 105.00 and paid 26.00. February: orders 3, 4, 5 total 60.50 + 57.50 + 22.00 = 140.00, of which only order 4 is paid.

Without FILTER you would write SUM(CASE WHEN status = 'paid' THEN ... ELSE 0 END), which does the same thing in a clunkier way.

12. Counting Correctly With DISTINCT

Last chapter warned about row multiplication. Here is how it bites in an aggregate. We want "number of orders per customer", joining down to the lines:

SELECT c.name,
       count(o.id)          AS wrong,
       count(DISTINCT o.id) AS right
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY c.id;

    name    | wrong | right
------------+-------+-------
 Sokha Chan |     3 |     2
 Dara Kim   |     3 |     2
 Linda Vong |     3 |     1
(3 rows)

Sokha has 2 orders but 3 lines (order 1 has two, order 2 has one), so count(o.id) counted each order once per line. count(DISTINCT o.id) counts each order id once. If you only need orders, do not join to the lines at all. If you need both orders and line-level numbers, use DISTINCT or aggregate lines in a subquery first (next chapter).

The same trap applies to SUM: summing an order-level column after joining to lines counts it once per line. Aggregate at the level the column belongs to.

13. Order of Evaluation, Again

Chapter 5 gave the logical order of a query. Now it has more steps:

1. FROM / JOIN    build the combined rows
2. WHERE          drop rows
3. GROUP BY       make groups
4. HAVING         drop groups
5. SELECT         compute output columns (aggregates, aliases)
6. ORDER BY       sort
7. LIMIT          cut

This explains the rules you met:

• WHERE cannot use aggregates: groups do not exist yet at step 2.
• HAVING can use aggregates: it runs after step 3.
• ORDER BY can use column aliases, but WHERE and HAVING cannot, because aliases are created in step 5, after them.

When a query fails with a puzzling error, walk through these steps and ask which step needs the thing you are using, and whether it exists yet. Save all your queries from this chapter into shopdb/queries/08_aggregation.sql.

14. Try It Yourself

Check each result against the seed data by hand where you can.

1. Find the average, minimum and maximum price of products in the Books category. (Join to categories, or use category_id = 2.) Round the average to 2 decimals.
2. Count the products that are out of stock (stock = 0) and the total units in stock across all products. Which products are they?
3. List every customer with the number of orders they placed (including 0 for those with none). Use LEFT JOIN and count(o.id). Then change count(o.id) to count(*) and explain why Vuthy and Maly now show 1.
4. Show only the customers whose total revenue (excluding cancelled orders) is above 60.00 using HAVING. Who qualifies?
5. For each category, show the number of distinct products and the list of product names with string_agg. Add a column with the average price rounded to 2 decimals.
6. For each order status, show count(DISTINCT customer_id) and the number of order lines. Which status has the most lines, and why does it not match the number of orders?

Where to go next. You can now build solid reports in one query. In Subqueries, CTEs and Window Functions you will learn to break big questions into named steps, save a report as a view, and rank rows or compute running totals without collapsing them into groups.