The Report That Counts People Twice
A training company has a Bookings table: BookingID, DelegateName, CourseCode, and FeePaid. One delegate can book several courses, so a person appears on multiple rows. The manager builds a report grouped by DelegateName and uses Count(*) in the group footer to show 'number of delegates'. The grand total reads 640, but the company only has 410 distinct delegates. Separately, the sum of FeePaid looks correct. He asks whether the table is broken or the report is.
1. Explain the discrepancy between 640 and 410 and identify which figure is which.
Read the worked answer
The table is fine; the report is measuring the wrong thing.
- 640 is the count of rows (bookings).
Count(*)counts every row, and delegates with multiple bookings are counted once per booking. - 410 is the count of distinct delegates.
So 640 is a booking count mislabelled as a delegate count. The fix is to count distinct values of the delegate key, e.g. Count(DISTINCT DelegateName) (or count distinct DelegateID if one exists - safer, since names can repeat). The sum of FeePaid is unaffected because fees are per booking, so summing rows is correct there.
2. The manager now wants 'average fee per delegate'. Why would `AVG(FeePaid)` over the rows give a misleading answer, and what should he compute instead?
Read the worked answer
AVG(FeePaid) averages per booking, so a delegate who booked five courses is weighted five times more than one who booked a single course. That answers 'average fee per booking', not 'average fee per delegate'.
To get average fee per delegate:
- Compute each delegate's total:
SUM(FeePaid)grouped by delegate. - Average those totals.
In one expression this is SUM(FeePaid) / Count(DISTINCT DelegateName), or a subquery that groups by delegate first and then averages. The judgement point: the unit of analysis changes the meaning of an average, and grouping must match the question being asked.
3. The manager adds a filter to show only courses with more than 20 bookings. Where should this condition go, and what common mistake must he avoid?
Read the worked answer
It is a condition on an aggregate (the booking count per course), so it belongs in a HAVING clause, not WHERE.
WHEREfilters individual rows before grouping.HAVINGfilters groups after aggregation.
The correct shape is roughly:
SELECT CourseCode, Count(*) AS Bookings
FROM Bookings
GROUP BY CourseCode
HAVING Count(*) > 20
The common mistake is writing WHERE Count(*) > 20, which fails or filters wrongly because the count does not exist until after grouping. Any per-group threshold - 'more than 20 bookings', 'total fees over £5,000' - must be expressed in HAVING.