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

The checkout count changes a wage total

Level 5: Repair it · Exercise 1 of 1

The wage report says Lincoln Park's staff earn 1,055.25 an hour between them. Four people work there.

This query should give each branch's wage_rate_sum from the listed hourly wages of its staff. It joins loans and reports Lincoln Park at 1,055.25. Fix it so it returns branch_id and wage_rate_sum, rounded to 2 decimals, for each branch with staff, ordered by branch id, and say why the total was inflated.

Finish line: 5 rows · columns branch_id, wage_rate_sum · in the order the question gives

Data: Twin Ports Tool Library.

I would like help with this exercise

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run your query to see the result here.

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