How can I count distinct values within each group?

Group by the event’s identifier and count distinct attendee IDs. In SQLite, the query is:

SELECT event_id,
       COUNT(DISTINCT attendee_id) AS distinct_attendees
FROM event_checkins
GROUP BY event_id
ORDER BY event_id;

GROUP BY event_id makes a group of rows for each event. COUNT(DISTINCT attendee_id) counts each different, non-NULL attendee ID once within that group. [S1]

For a made-up example, assume the table contains these rows:

event_id | attendee_id
---------|------------
101      | A7
101      | A7
101      | B2
101      | NULL
202      | C4
202      | C4
202      | D9

The query returns:

event_id | distinct_attendees
---------|-------------------
101      | 2
202      | 2

At event 101, A7 appears twice but contributes one distinct ID; B2 contributes another. The NULL attendee ID is not counted. These results illustrate the query using only the assumed sample rows, not a claim about any actual event data.

COUNT(*) counts every row in each group, so it includes duplicate check-ins and rows with a NULL attendee ID. COUNT(DISTINCT attendee_id) instead removes duplicate attendee-ID values for the count and does not count NULL attendee IDs. [S1]

For the assumed sample, adding COUNT(*) alongside the distinct count makes the difference visible:

SELECT event_id,
       COUNT(*) AS checkin_rows,
       COUNT(DISTINCT attendee_id) AS distinct_attendees
FROM event_checkins
GROUP BY event_id
ORDER BY event_id;

event_id | checkin_rows | distinct_attendees
---------|--------------|-------------------
101      | 4            | 2
202      | 3            | 2

These columns answer different questions: the first counts records in the group; the second counts unique non-NULL attendee IDs. Choose based on what the number is meant to represent.

Use the column that identifies the group in both the selected output and GROUP BY, and put the value you want to deduplicate inside COUNT(DISTINCT ...). For example, if your table has an event_name column and each name identifies one event, you could write:

SELECT event_name,
       COUNT(DISTINCT attendee_id) AS distinct_attendees
FROM event_checkins
GROUP BY event_name
ORDER BY event_name;

For a different distinct count, replace attendee_id with the relevant column. For example, counting unique ticket codes per event would use COUNT(DISTINCT ticket_code) while still grouping by event_id.

Choose the grouping column with care: if separate events can share a name, grouping only by name combines their rows. In that case, group by the event ID to keep those events separate, and include a name in the output only if your query and data design support that choice. The example assumes the column names exist; adapt them to your schema.

First, confirm what one output row should represent—for example, one event ID—and what value should be unique—for example, one attendee ID within that event. Then inspect a small set of source rows that includes a duplicate and, if relevant, a NULL. Compare the query’s distinct count with the unique non-NULL values visible in each group.

A useful review is to run both COUNT(*) and COUNT(DISTINCT attendee_id) temporarily, as in the earlier example. If they match, that does not by itself prove the grouping is correct; it only shows the two counts are equal for those rows. Check that the selected grouping key keeps separate groups separate, and verify that excluding NULL IDs is appropriate for your intended definition of an attendee.

SQLite’s documented distinction establishes how these aggregate counts treat rows and values. It does not establish that your chosen columns correctly represent events or attendees; that depends on your table’s meaning and data.