“There are two records” and “there are two measured values” can be different statements. This workshop uses five fictional SQL rows to make that distinction visible. You will calculate grouped counts and sums, apply a condition to the grouped result and explain why an empty measurement is not automatically zero.
Understand the operation being practised
Software Carpentry’s aggregation lesson covers grouped summaries and missing values. COUNT(*) counts rows, while COUNT(value) counts non-null values.
PostgreSQL’s aggregate tutorial distinguishes filtering input rows with WHERE from filtering completed groups with HAVING.
Basic SELECT familiarity is enough. The worked answer uses a small SQLite-style table named observations with columns group_name and value. You can reason through it without connecting to a database. Do not paste the example into a database containing real records; a disposable local fixture is sufficient if you choose to execute it.
Predict every group before writing the query
| Row | group_name | value |
|---|---|---|
| 1 | A | 10 |
| 2 | A | NULL |
| 3 | B | 5 |
| 4 | B | 15 |
| 5 | C | NULL |
The question is: which groups have at least two observed values, and what is their total? “Observed” is important. Group A has two rows but only one measurement. Group C exists in the table, yet its row supplies no measured number. Replacing NULL with zero would add an assumption that is absent from the fixture.
Work the summary and condition
SELECT group_name,
COUNT(*) AS row_count,
COUNT(value) AS observed_count,
SUM(value) AS observed_total
FROM observations
GROUP BY group_name
ORDER BY group_name;
group | row_count | observed_count | observed_total
A | 2 | 1 | 10
B | 2 | 2 | 20
C | 1 | 0 | NULL
A contributes one observed value, ten. B contributes five plus fifteen, giving twenty. C has no observed value to sum in this example, so the result remains NULL. Reconcile row counts two plus two plus one with five input rows, and observed counts one plus two plus zero with three supplied numbers.
SELECT group_name, SUM(value) AS observed_total
FROM observations
GROUP BY group_name
HAVING COUNT(value) >= 2
ORDER BY group_name;
The filtered answer is B with a total of twenty. Group A is excluded because its observed count is one. A condition based on COUNT(*) would answer a different question and would incorrectly include A for the stated task.
Explain two plausible mistakes
First, filtering out NULL rows before examining the summary would make C disappear. That may be useful for another question, but it would hide the distinction between a group with no observations and a group absent from the data. Preserve the unfiltered summary as evidence for this exercise.
Second, substituting zero for NULL would make C appear to contain a measured zero. The arithmetic might become easier to display, but the interpretation would change. Missingness and an observed zero are separate states. If a later analysis adopts a replacement rule, document it explicitly and keep the original result for comparison.
Check the handover and extend the fixture
Submit both queries, the unfiltered summary and the reason only B survives. For an extension, replace A’s NULL with an explicitly supplied zero. A then has two observed values and total ten, so both A and B pass. The change is new evidence in a new fixture, not a repair justified by the original rows.
The join-cardinality workshop explores another reason totals can mislead. The group-comparison exercise examines denominators when comparing summaries. Continue in the workshop collection.
Questions about grouped missing values
Why does A not pass the condition?
The condition asks for two observed values, not two rows. A has only one non-null value.
Is C’s total the same as a measured zero?
No. Its input contains no measured number. The fixture gives no basis for asserting a zero observation.
Why keep the unfiltered summary?
It makes exclusions explainable. A reviewer can see that C existed and why the grouped condition removed it.
Official reference
Checked 17 October 2026: Software Carpentry: Aggregation and PostgreSQL: Aggregate Functions. The five-row fixture and worked answer are original.
Related guides
View the worked-example diagram

