Why does a skills join inflate team headcount?

Prepare the diagnostic input

A member of staff can have several skills. The joined rows describe skill assignments, so COUNT(*) no longer measures people. Count distinct staff IDs within each team; retain staff with no recorded skill so missing training data does not remove them from the roster.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE staff(id TEXT PRIMARY KEY,team TEXT);
CREATE TABLE skills(staff_id TEXT,skill TEXT);
INSERT INTO staff VALUES ('S1','Ops'),('S2','Ops'),('S3','Sales');
INSERT INTO skills VALUES ('S1','packing'),('S1','receiving'),('S2','packing');

Reproduce the misleading result and correct it

SELECT s.team,COUNT(*) AS headcount FROM staff s
LEFT JOIN skills k ON k.staff_id=s.id GROUP BY s.team ORDER BY s.team;

SELECT s.team,COUNT(DISTINCT s.id) AS headcount FROM staff s
LEFT JOIN skills k ON k.staff_id=s.id GROUP BY s.team ORDER BY s.team;

Ops has two people but three skill rows; Sales has one person without skills. The misleading query reports three Ops staff. The correction returns two and one. A distinct skill count would answer a different question and would still not give headcount.

Check the boundary before applying the fix

Give S1 a third skill. Ops headcount must remain two, because no additional employee was hired.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO skills VALUES ('S1','returns');

This is a roster count, not capacity or full-time-equivalent staffing. Effective employment dates and team transfers need separate rules.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.