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

Replacement value per branch

Level 2: Fill the blank · Exercise 1 of 1

The director wants the replacement value of the tools each branch owns. The value is stored once per tool. Fill in the table to join, so that nothing is repeated.

Finish line: 6 rows · columns branch, replacement_value · in the order the question gives

Data: Twin Ports Tool Library.

I would like help with this exercise

SELECT b.name AS branch, ROUND(SUM(t.replacement_cost), 2) AS replacement_value FROM branches b JOIN t ON t.branch_id = b.id GROUP BY b.id, b.name ORDER BY b.id;

Blank 1 of 1: the table where replacement_cost is stored once per tool

Open in the playground

Query results

Check to see the result here.

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