Joins and aggregation

Why this matters

On Day 43 you split one spreadsheet into two tables so no fact was stored twice. That was the right call, and it left you with a problem: the questions people actually ask span both tables. "Which equipment has never been serviced?" touches equipment and maintenance records at once.

Today you learn to put the tables back together for the length of one query, and to summarise many rows into one number. By the end you can produce the report your API's dashboard will need — every piece of equipment, its service count, and its last service date — including the equipment with no records at all, which is the part almost everyone gets wrong.

Joining two tables

A join matches rows from one table with rows from another, using a condition that says which rows belong together. That condition is almost always "the foreign key equals the primary key".

SELECT e.name, m.performed_on, m.notes
FROM maintenance_records m
INNER JOIN equipment e ON e.id = m.equipment_id
ORDER BY m.performed_on;

       name       | performed_on |               notes
------------------+--------------+-----------------------------------
 Feed Pump 3      | 2024-02-11   | Replaced mechanical seal
 Chiller 1        | 2024-05-19   | Coil clean and refrigerant top-up
 Feed Pump 3      | 2024-09-03   | Checked shaft alignment
 Air Compressor A | 2025-01-08   | Rebuilt compressor valve plate
 Feed Pump 3      | 2025-03-22   | Bearing replacement
(5 rows)

Two pieces of syntax to read carefully. maintenance_records m gives the table the short alias m, so m.notes means "the notes column of that table". Aliases are not decoration: both tables have an id column, and without a prefix the database cannot tell which you mean. ON e.id = m.equipment_id is the join condition — the rule for pairing rows.

Notice Feed Pump 3 appears three times. The join produces one row per pair, so a piece of equipment with three records contributes three rows. The equipment's name is repeated in the result, but it is still stored only once on disk.

INNER JOIN and LEFT JOIN

The join above was an INNER JOIN: it returns only rows that found a match on both sides. Watch what that discards.

SELECT e.name, m.id AS record_id, m.performed_on
FROM equipment e
LEFT JOIN maintenance_records m ON m.equipment_id = e.id
ORDER BY e.name, m.performed_on;

       name       | record_id | performed_on
------------------+-----------+--------------
 Air Compressor A |         4 | 2025-01-08
 Chiller 1        |         3 | 2024-05-19
 Conveyor B       |           |
 Feed Pump 3      |         1 | 2024-02-11
 Feed Pump 3      |         2 | 2024-09-03
 Feed Pump 3      |         5 | 2025-03-22
 Feed Pump 4      |           |
(7 rows)

A LEFT JOIN keeps every row from the left table — the one named in FROM — whether or not it matched. Where there was no match, the right table's columns come back as NULL, which psql prints as an empty space. Conveyor B and Feed Pump 4 are here with blank record columns. An INNER JOIN would have dropped them entirely, and your report would silently claim those two machines do not exist.

That is the whole decision. Ask yourself: do I want rows from the left table that have no partner? If the answer is yes — never-serviced equipment, customers with no orders, users with no posts — you need LEFT JOIN. If the answer is no, INNER JOIN says so more clearly.

COUNT and GROUP BY

An aggregate function collapses many rows into one value: count, sum, avg, min, max. On its own, an aggregate reduces the whole result to a single row.

GROUP BY splits the rows into groups first, then applies the aggregate to each group separately, giving one output row per group.

SELECT e.name, count(m.id) AS record_count, max(m.performed_on) AS last_service
FROM equipment e
LEFT JOIN maintenance_records m ON m.equipment_id = e.id
GROUP BY e.id, e.name
ORDER BY e.name;

       name       | record_count | last_service
------------------+--------------+--------------
 Air Compressor A |            1 | 2025-01-08
 Chiller 1        |            1 | 2024-05-19
 Conveyor B       |            0 |
 Feed Pump 3      |            3 | 2025-03-22
 Feed Pump 4      |            0 |
(5 rows)

That is the deliverable report. Group by e.id as well as e.name, not just the name: id is what actually identifies the equipment, so two machines that happen to share a name still get their own row.

The rule that trips beginners: every column in the SELECT list must either appear in GROUP BY or be wrapped in an aggregate. Otherwise the database has many values and one output slot, and it tells you so — ERROR: column "e.serial_number" must appear in the GROUP BY clause or be used in an aggregate function.

HAVING filters groups after aggregation, where WHERE filters rows before it:

SELECT e.name, sum(m.hours_spent) AS total_hours, round(avg(m.hours_spent), 2) AS avg_hours
FROM equipment e
JOIN maintenance_records m ON m.equipment_id = e.id
GROUP BY e.id, e.name
HAVING sum(m.hours_spent) > 2
ORDER BY total_hours DESC;

       name       | total_hours | avg_hours
------------------+-------------+-----------
 Feed Pump 3      |        4.25 |      1.42
 Air Compressor A |        4.00 |      4.00
 Chiller 1        |        3.25 |      3.25
(3 rows)

(JOIN with no qualifier means INNER JOIN. Writing INNER is clearer.)

Reading an aggregate correctly

An aggregate is a number, which makes it look more trustworthy than it is. Two traps do most of the damage.

count(*) counts rows; count(column) counts non-null values. After a LEFT JOIN, an unmatched piece of equipment still produces one row, so count(*) reports 1 for a machine that has never been serviced:

SELECT e.name, count(*) AS wrong_count, count(m.id) AS right_count
FROM equipment e
LEFT JOIN maintenance_records m ON m.equipment_id = e.id
GROUP BY e.id, e.name
ORDER BY e.name;

       name       | wrong_count | right_count
------------------+-------------+-------------
 Air Compressor A |           1 |           1
 Chiller 1        |           1 |           1
 Conveyor B       |           1 |           0
 Feed Pump 3      |           3 |           3
 Feed Pump 4      |           1 |           0
(5 rows)

Three columns agree and two do not, which is exactly how this bug survives review. After a LEFT JOIN, always count a column from the right table.

Aggregates other than count ignore NULL and return NULL for an empty group. sum over no rows is not 0; it is "nothing to add up". If your API must send a number, say so explicitly with coalesce, which returns its first non-null argument:

SELECT e.name, coalesce(sum(m.hours_spent), 0) AS total_hours
FROM equipment e LEFT JOIN maintenance_records m ON m.equipment_id = e.id
GROUP BY e.id, e.name ORDER BY e.name;

       name       | total_hours
------------------+-------------
 Air Compressor A |        4.00
 Chiller 1        |        3.25
 Conveyor B       |           0
 Feed Pump 3      |        4.25
 Feed Pump 4      |           0
(5 rows)

Walkthrough: build the report in four steps

Never write a join and an aggregate in one go. Add one clause and look.

1. Join and look at the raw pairs. Run the LEFT JOIN from earlier with no grouping. Seven rows. Count the blanks: two.
2. Switch to INNER JOIN and rerun. Five rows, and the two blanks are gone. You have just seen, on your own data, what the join type decides.
3. Go back to LEFT JOIN and add GROUP BY e.id, e.name with count(m.id). Five rows again — one per machine — with 0 for the two.
4. Add max(m.performed_on) AS last_service. Blank for the never-serviced pair. Blank is the honest answer: there is no last service date, and 1970-01-01 or 0 would be a lie.

How to use AI today

Today's mode is tutor: ask it to explain the concept, give a small example, then let you attempt it — not to hand you the finished query. A join you did not reason through is one you cannot fix when the counts look wrong.

Your turn

Produce db/reports.sql: join queries that correctly handle missing related rows.

1. Reload db/seed.sql so your data matches this lesson.
2. Write an INNER JOIN listing every maintenance record with its equipment name, oldest first. Record the row count.
3. Change it to a LEFT JOIN from equipment. Record the new count and name the rows that appeared.
4. Write the report: equipment name, count(m.id) as record_count, max(m.performed_on) as last_service, grouped by e.id, e.name, ordered by name. All five machines must appear.
5. Deliberately break it — remove e.id from the GROUP BY, or select e.status without grouping it — and copy the exact error into a comment. Then fix it.
6. Add a query using sum and coalesce so equipment with no records reports 0 hours, not a blank.
7. Add one using HAVING to show only equipment with more than one maintenance record.
8. Above each query, write the English question it answers and the row count you observed.

Common pitfalls

• Reaching for INNER JOIN by habit. It is the default in most people's fingers and it silently deletes exactly the rows a "which of these has none?" question is about.
• count(*) after a LEFT JOIN. Reports 1 where the answer is 0. Count a right-table column.
• Putting a right-table condition in WHERE instead of ON. `LEFT JOIN ... WHERE m.performed_on

  '2025-01-01'throws away the unmatched rows again, because theirNULLfails the test. Move that condition into theON` clause.

• Expecting sum to be 0 for an empty group. It is NULL. Wrap it in coalesce when the API needs a number.

Verify it yourself

Open today's reference, the PostgreSQL tutorial, and read its section on joins between tables.

1. The tutorial covers RIGHT JOIN and FULL OUTER JOIN. Write down, in your own words, how a RIGHT JOIN differs from a LEFT JOIN, and why you can always rewrite one as the other.
2. This lesson claimed a LEFT JOIN fills unmatched right-hand columns with NULL. Find the documentation's own statement and record whether it agrees.

Add both as comments in db/reports.sql. Tomorrow you make multi-step changes safe.