Question 4 of 6 Intermediate Concept

What is the difference between COUNT(*) and COUNT(column)?

Short answer

COUNT(*) counts rows, including rows whose columns are NULL. COUNT(column) counts rows with a non-NULL value in that column. Neither automatically counts unique patients.

Why

In a LEFT JOIN between 20 enrolled subjects and optional measurements, COUNT(*) counts the result rows. COUNT(measurement_value) counts non-NULL measured values, but a lab record with a NULL value does not contribute even if the result record exists. COUNT(DISTINCT subject_id) counts distinct subject IDs among the selected rows, with caveats for NULL subject IDs.

For example, five subjects without labs can appear as five NULL-extended rows: those rows contribute to COUNT(*) but not COUNT(lab_value). Multiple lab records per subject further change the meaning. Determine whether the desired metric is number of enrolled people, test records, valid measurements, or distinct tested people and specify filters and grain accordingly.

Test edge cases containing missing and duplicate observations.

Common mistakes

Treating COUNT(lab_value) as a count of study participants or using COUNT(*) as a count of unique entities

The interviewer might ask next

How do you count participants with at least one valid result without inflating repeated visits?