Practise · The checkout count changes a wage total

Opening your saved work…

The reading

The multiplication trap

Grandma's Marathon stores the official finisher count for each race. Ten marathons, and their counts add to 63,218. Join the results table to get the year labels, sum the same column, and the total becomes 2,442,010. Nothing failed. Each race's count was repeated once per result row, and SUM added every repeat.

SELECT s.branch_id, ROUND(SUM(s.hourly_wage), 2) AS wage_rate_sum
FROM staff s
GROUP BY s.branch_id
ORDER BY s.branch_id;

Wages summed where they live, once per person. Join loans and Lincoln Park's 96.25 becomes 1,055.25. Open in the playground

The same thing happens at the Tool Library. A staff member's hourly wage is one fact per person. Join staff to loans and that wage appears once per checkout; sum it and Priya's wage is counted thirteen times. The total grows every time someone borrows a hammer, which is not what a wage total should do.

The fix is not SUM(DISTINCT). Two different employees can earn the same wage and both must count. The fix is grain: aggregate each fact at the level where it is stored once, and join only after. Sum wages from staff alone. Count loans from loans alone. Combine the two summaries if the question needs both.

The habit that catches this is a single comparison: compute the total from the parent table by itself, then from the join. If they differ, you have multiplied something.

Rule of thumb. A one-to-many join repeats the one side. Sum a fact at its own grain, then check the joined total against the unjoined one.

Going deeper (optional, not on the exam)

The sum of listed hourly wages is a comparison baseline, not payroll expense: hours worked are not in this database. Say what a number is before you add it up.

The checkout count changes a wage total · count repetitions before adding money · level 1 of 6

Loans checked out by each staff member

Level 1: Watch it answered · Exercise 1 of 1

How many loans has each staff member checked out? Watch the count, then run it. This is the number that will multiply a wage in a moment.

Data: Twin Ports Tool Library.

Why it works, clause by clause

FROM staff s LEFT JOIN loans l ON l.staff_id = s.id: one row per loan, with the staff member attached; a staff member with no loans keeps one placeholder row.
COUNT(l.id) per staff member: how many loans each one checked out.
Read the numbers. Priya has 13. If her wage were on each of those rows, a SUM would count it 13 times.

From the reading: The multiplication trap, paragraph 2

I would like help with this exercise

SELECT s.id AS staff_id, s.first_name, COUNT(l.id) AS matching_loans
FROM staff s
LEFT JOIN loans l ON l.staff_id = s.id
GROUP BY s.id, s.first_name
ORDER BY s.id;

Open in the playground

Query results

Run it to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.