SQL COUNT DISTINCT: Count Unique Values Within Each Group

The practice task

Count unique known participants per workshop in a fictional attendance log. Keep the raw row count alongside the unique count to reveal repeated records and an unidentified attendee.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE workshop_log(workshop TEXT, person_id INTEGER);
INSERT INTO workshop_log VALUES ('SQL',1),('SQL',1),('SQL',2),('SQL',NULL),('Writing',1),('Writing',3);

SELECT workshop, COUNT(DISTINCT person_id) AS known_people,
 COUNT(*) AS log_rows FROM workshop_log
GROUP BY workshop ORDER BY workshop;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

SQL has four log rows but only two distinct known participant IDs. The repeated ID 1 counts once. The missing ID does not become a third known person. Writing has two rows with two different known IDs.

A unique participant count does not tell you whether the repeated SQL row was a duplicate submission, a second session or a legitimate repeat entry. Investigate the event definition separately before removing source records.

For uniqueness across a combination such as workshop and person, build a distinct rowset at that grain first. Concatenating columns into a string can create collisions or ambiguous handling of missing values. A separate SELECT DISTINCT on the required columns makes the identity rule inspectable.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Use a stable identity rather than a display name when available.
• NULL is not counted as a unique known identity here.
• Do not add unique counts from overlapping groups to claim a global unique total.