Prepare the diagnostic input
A scan is evidence of a check-in attempt, not a distinct attendee. Group by session and count distinct member IDs. The same person in a different session must count again; deduplicating the entire export by member would undercount later sessions.
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.
| id | session_id | member_id |
|---|---|---|
| 1 | A | M1 |
| 2 | A | M1 |
| 3 | A | M2 |
| 4 | B | M1 |
CREATE TABLE scans(id INTEGER PRIMARY KEY,session_id TEXT,member_id TEXT);
INSERT INTO scans VALUES (1,'A','M1'),(2,'A','M1'),(3,'A','M2'),(4,'B','M1');Reproduce the misleading result and correct it
SELECT session_id,COUNT(*) AS attendees FROM scans GROUP BY session_id ORDER BY session_id;| session_id | attendees |
|---|---|
| A | 3 |
| B | 1 |
SELECT session_id,COUNT(DISTINCT member_id) AS attendees FROM scans GROUP BY session_id ORDER BY session_id;| session_id | attendees |
|---|---|
| A | 2 |
| B | 1 |
Session A has three scans but two members; B has one member. The corrected counts are two and one. It does not delete the repeated M1 scan, so the event can still be inspected for device retries or staff corrections.
Check the boundary before applying the fix
Add another M1 scan in A. Attendance stays two. Adding a new member instead would increase it, so the key is person plus session, not the scan count.
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 scans VALUES (5,'A','M1');| session_id | attendees |
|---|---|
| A | 2 |
| B | 1 |
NULL member IDs are ignored by COUNT(DISTINCT). Unidentified scans need a separate review, and check-out or no-show policy is outside this count.
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.