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 3 of 6

The finisher count that grew

Level 3: Predict the result · Exercise 1 of 1

Grandma's Marathon stores an official total_finishers for each race. The ten marathons add up to 63,218. Someone joins results to get the year labels and sums the same column. Before you run it, what does the sum become?

SELECT SUM(ra.total_finishers) AS finishers
FROM races ra
JOIN results s ON s.race_id = ra.id
WHERE ra.race_type = 'Marathon';

The query. Predict before you run it.

Data: Grandma’s Marathon.

I would like help with this exercise

Opens once your prediction is right, or after the worked answer.

Query results

The real result appears here after you run it.

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