Data & Decisions
NULL, Zero, and Missing Rows Mean Different Things in an Analysis
Keep observed zero counts, unknown values and absent expected rows separate. Run through a complete synthetic SQL dataset, compare the denominators changed by COALESCE and use a filled reporting decision that preserves incomplete coverage.
Keep an observed zero, an unknown value and an absent record separate until the business definition explains each one. Zero contributes a measured value to an average. SQL NULL usually does not contribute a numeric input. A missing row cannot be counted or classified unless you know which rows should exist. Replacing every blank with zero can change the question while making the report look more complete.
This LandOffer.ai guide develops an original small dataset and actually executes its SQL comparisons in an isolated in-memory SQLite database. The organization, dates and source-status explanations are fictional. We did not inspect a production database or run PostgreSQL. The results demonstrate these specific queries and assumptions, not the correctness of any real source system.

Define the business meaning before choosing a replacement
Start with the row's grain: one day, order, account or measurement? Then define what a numeric value represents. An observed count of zero returned tools means that no tools were returned during the measured period. It does not mean the workshop was closed, the collector failed or no other work happened.
A NULL in an existing count field records the absence of a numeric value; it does not, by itself, explain that absence. The business status must supply the reason rather than the analyst guessing it. It might mean not measured, not yet available, not applicable, withheld or failed validation. Your source contract and an accompanying status field should distinguish the relevant reasons. Do not assume that every system uses NULL identically.
A missing row is different again. The entity or date may not belong in the population, or a required record may be absent. A failed load, join-key mismatch or filter could also hide it. Those are possibilities to investigate; the absence itself does not identify the cause.
PostgreSQL's current aggregate documentation, identified as version 18 when checked, defines AVG over non-null inputs and distinguishes COUNT(*) from counting an expression. Those SQL semantics explain why the same numeric values can produce different denominators after a transformation. They cannot decide what a missing business observation should mean.
Worked asset: a dataset with zero, NULL and an absent day
The fictional Hearth tool-lending workshop wants returns per scheduled operating day. Its schedule includes five eligible days, November 2–6, 2026. November 7 is explicitly closed and ineligible. These future dates are teaching inputs, not observations already collected from an employer.
The intended grain is one count record per eligible date. Each source date is a non-null unique key. Counts must be nonnegative integers when present; a separate source status explains a pending value. The full source contains only four rows, even though the table below aligns it with the schedule for inspection.
| Schedule date | Eligible? | Source row exists? | Stored count | Stipulated meaning |
|---|---|---|---|---|
| November 2 | Yes | Yes | 4 | Observed four returns |
| November 3 | Yes | Yes | 0 | Observed no returns |
| November 4 | Yes | Yes | NULL | Row arrived; count still pending |
| November 5 | Yes | No | No stored value | Expected record missing; reason unknown |
| November 6 | Yes | Yes | 8 | Observed eight returns |
| November 7 | No | No | No stored value | Closed date outside this metric's population |
The schedule is necessary evidence for the missing November 5 row. Looking only at four source rows would not reveal whether a fifth date was required. Conversely, generating a complete calendar and labeling every absent date an error would incorrectly include the closed November 7 date.
We create these tables locally and execute the following aggregate over the source rows:
SELECT
COUNT(*) AS source_rows,
COUNT(returned_count) AS known_values,
SUM(returned_count) AS observed_sum,
AVG(returned_count) AS known_day_mean
FROM daily_returns;
The actual result is four source rows, three known counts, observed sum 12 and known-day mean 4.0. The average is (4 + 0 + 8) / 3. The observed zero remains in the denominator. The NULL November 4 count and absent November 5 row contribute no numeric input.
Calling 4.0 “average returns per scheduled day” would be misleading because two of the five eligible dates lack known counts. It is the mean across the three observed eligible days. There is no evidence here that those days represent the two unresolved ones. For example, if difficult reporting days also tend to have more returns, averaging only reported dates could give a distorted impression. That is a possible mechanism to investigate, not a cause demonstrated by this synthetic dataset. The sum of 12 is also an observed subtotal, not an established complete-period total. This label lets the operations owner use the confirmed work count without treating unresolved dates as settled.
Make the expected population visible in the join
PostgreSQL's table-expression guidance explains that a left join retains an unmatched left-side row and supplies nulls for the right-side columns. Use the schedule on the left so the expected date survives even when its count record is absent.
SELECT
s.report_date,
d.returned_count,
CASE
WHEN d.report_date IS NULL THEN 'missing_row'
WHEN d.returned_count IS NULL THEN 'unknown_value'
WHEN d.returned_count = 0 THEN 'observed_zero'
ELSE 'observed_positive'
END AS state
FROM schedule s
LEFT JOIN daily_returns d
ON d.report_date = s.report_date
WHERE s.eligible = 1
ORDER BY s.report_date;
This executed query produces two observed-positive dates, one observed-zero date, one unknown-value date and one missing-row date. Testing the joined source key first is important: d.returned_count IS NULL alone would match both the real NULL and the null introduced for an unmatched row. The source key is explicitly non-null, so it can serve as a presence marker here. Its unique-date constraint also prevents multiple source rows from multiplying a schedule day. Recheck both assumptions when adapting this join to a different table; a nullable presence marker would lose the distinction.
After the join, COUNT(*) is five because it counts the retained eligible schedule rows. COUNT(d.report_date) is four because it counts matches. COUNT(d.returned_count) is three because it counts known values. Known-value coverage is therefore 3/5, or 60%, while matching-row coverage is 4/5, or 80%. Receiving a row is not the same as receiving its measurement.
Keep filters from hiding those distinctions. In our actual negative example, adding AND d.returned_count >= 0 to the WHERE clause leaves only three rows: the observed 4, 0 and 8. The source NULL and unmatched row do not pass that predicate. That query can select known nonnegative measurements, but cannot then claim that the five-day population was fully observed.
Worked asset: compare what each treatment actually measures
PostgreSQL's conditional-expression reference says COALESCE returns the first non-null argument. It is a substitution mechanism, not evidence that the substituted value is true. Its location also matters: applying it to existing rows cannot create an absent row.
We execute three treatments on the same fictional source, with no source records repaired:
| Treatment | Actual mean | Denominator and implied assumption |
|---|---|---|
AVG(returned_count) on source |
4.0 | Three known values, including the observed zero |
AVG(COALESCE(returned_count, 0)) on source |
3.0 | Four existing rows; silently treats the pending count as zero, still omits November 5 |
| Same zero substitution after eligible schedule left join | 2.4 | Five expected dates; treats both pending count and missing row as zero |
The numerator remains 12 in all three calculations. The means change because the denominator expands from three to four to five with assumed zeros. A lower number does not demonstrate that tool returns fell. It demonstrates a change in treatment of incomplete data.
Analyst Beatrix completes this fictional decision memo for operations owner Hollis: “Publish the observed subtotal of 12 and the observed-day mean of 4.0 only with three-of-five known-day coverage. Mark the scheduled-day mean and full-period total incomplete. Do not replace November 4 or November 5 with zero. Exclude the explicitly closed November 7 date from this metric.”
The associated action record is filled rather than left as a generic request to clean data:
| Record | Named action | Current decision and remaining evidence |
|---|---|---|
| November 3 observed zero | Beatrix retains zero and its observed status | Keep it in the known-value denominator; no repair requested |
| November 4 pending count | Source owner Greer checks the pending measurement and its source receipt | Unknown until a valid count is supplied; no invented replacement |
| November 5 absent record | Pipeline owner Florian checks the expected-date key, extraction and load evidence | Missing expected row remains open; a loader failure is not yet established |
| November 7 closed date | Hollis confirms the stipulated schedule rule for this fictional report | Excluded as ineligible, not inserted as a measured zero |
| Scheduled-day metric | Beatrix preserves the current snapshot and labels coverage | Beatrix recomputes after Greer or Florian supplies reviewed evidence, records the changed input and preserves the earlier incomplete snapshot; no automatic backfill is authorized |
This is a completed reporting decision with unresolved source work. The example does not claim that Greer recovered a count or Florian repaired a pipeline. The local database stays at its original four source rows after all checks.

Distinguish no selected rows from all-NULL rows
An aggregate result can hide different underlying situations. Selecting November 5 from the source yields no rows: COUNT(*) is zero, COUNT(returned_count) is zero, and both SUM and AVG are NULL. Selecting November 4 yields one row with a NULL count: COUNT(*) is one, the non-null count is zero, and SUM and AVG are again NULL.
Those are actual results of the isolated SQLite queries. SQLite's aggregate reference documents its non-null input behavior; its sum and nonstandard total are not interchangeable on empty input. We used SUM, not total. PostgreSQL's aggregate reference likewise notes that summing no rows returns NULL rather than zero. Do not assume an engine's default or alternate aggregate supplies business completeness. A verified complete event ledger with no returns might justify a business zero, but that requires evidence about the ledger and period. An empty query result alone does not establish that condition.
For a display, an explicit “No matching records” or “Count pending” label can be more informative than zero. If a downstream format needs a numeric fallback, keep an accompanying status and the original value. Separate display convenience from analytical imputation so another calculation does not silently inherit the fallback as a measurement.
Verify the query and the semantics separately
The actual runtime is Python 3.12.14 with SQLite 3.54.1. We retain the setup, schedule and source CSVs, each executed query and expected-result checks. Duplicate date, negative count and nonnumeric count insertions are rejected by the authored fixture constraints and rolled back. These controls protect this small exercise's grain and numeric domain; they do not validate a real collection process.
Before using the approach elsewhere, check the actual engine, data types, row key and joins. A one-to-many join can duplicate a daily count and change its weighting even if every value is non-null. An empty string or text placeholder also has different behavior from SQL NULL. Inspect the typed data rather than relying on a spreadsheet's blank-looking cells.
Finish with a report that exposes its denominator, coverage and remaining questions. Ask for missing source evidence before asserting zero or a complete total. For more practical analysis records, continue with LandOffer.ai's workplace guides; keep the meaning of absence attached whenever a metric is reused.
Sources and Further Reading
- PostgreSQL 18: Aggregate Functions — non-null averages, row versus expression counts and empty-input sums.
- PostgreSQL 18: Conditional Expressions —
CASEand first-non-nullCOALESCEbehavior. - PostgreSQL 18: Table Expressions — outer-join rows and filter placement.
- SQLite: Built-in Aggregate Functions — actual runtime's aggregate semantics. Current primary bodies checked October 27, 2026; exact page publication dates are not displayed. PostgreSQL documentation was reviewed, while execution occurred only in the stated local SQLite fixture.


