The Relationship That Multiplied the Totals
A club has two tables: Members(MemberID primary key, MemberName, SubscriptionPaid) with 150 rows, and Payments(PaymentID primary key, MemberID foreign key, Amount, PaymentDate) with 900 rows - members pay in instalments, so several payments per member. A staff member creates a query joining the two tables on MemberID and includes SubscriptionPaid and Amount. He then sums SubscriptionPaid and gets a figure far larger than the true total of subscriptions. He concludes the subscription data is corrupted. A colleague disagrees and points to the join.
1. Explain why summing `SubscriptionPaid` in this joined query overstates the total, and identify the concept at work.
Read the worked answer
The join is one-to-many: each member matches several payment rows. So each member's single SubscriptionPaid value is repeated once per payment in the joined result. Summing that column counts the same subscription again for every instalment.
This is a fan-out / repeated-row effect of joining tables at different levels of detail. The subscription total is inflated roughly in proportion to the number of payments per member - not because the data is corrupted, but because the join multiplied the rows.
The staff member's conclusion is wrong; the query is the problem.
2. Give two correct ways to obtain the true total of subscriptions, and say which is preferable and why.
Read the worked answer
Option A - aggregate from the single table:
SELECT Sum(SubscriptionPaid) FROM Members
No join, no fan-out. This is the simplest correct answer.
Option B - aggregate before joining: create a subquery that sums payments per member, then join that one-row-per-member result to Members. Now each member appears once, so SubscriptionPaid is not duplicated.
Preferable: Option A when only the subscription total is needed - it is simpler and impossible to inflate. Option B is right when you genuinely need both subscription and payment totals side by side per member, because it keeps each table's figures at its own level of detail before combining.
3. The staff member wants a per-member view showing subscription paid and total payments received. What must the query do to avoid the same inflation, and what should he check?
Read the worked answer
He must pre-aggregate payments to one row per member before joining, e.g.:
SELECT m.MemberName, m.SubscriptionPaid, p.TotalPaid
FROM Members m
LEFT JOIN (
SELECT MemberID, Sum(Amount) AS TotalPaid
FROM Payments GROUP BY MemberID
) p ON m.MemberID = p.MemberID
A LEFT JOIN keeps members who have made no payments yet (their total shows as null/zero) - worth checking, since an inner join would silently drop them.
Checks to run:
- Row count should equal the number of members (150), not payments.
- The grand total of
SubscriptionPaidshould match the single-table sum. - Members with no payments appear with zero, not missing.
If the row count exceeds 150, the fan-out is still present.