How Much Inspection Time Did We Actually Record?

The supervisor wants a total and an average inspection duration. Botie has six visit records, but one has no minutes entered. Another records zero. Dividing a known total by every row would silently assign a zero contribution to the unknown visit.

Before writing an aggregate, decide what it is summarising. SQL provides separate operations for counting rows, counting nonmissing values and combining those values. We will keep those quantities visible together.

Filter to the requested finding before summing known durations. An entirely unknown selection must remain unknown.

[
  {
    "total_minutes": 55
  }
]

The source grain is one inspection. An aggregate query without GROUP BY creates one summary over its input rows. The result no longer describes a particular visit.

That is why an inspection ID beside a total needs explanation. Which visit would it refer to? Portable aggregate queries select aggregates or explicit grouping keys rather than an unrelated row’s ID. SQLite permits some bare-column forms, but they can conceal an unintended result.

The summary’s grain is the selected collection of visits.

COUNT(*) counts input rows. COUNT(minutes) counts rows where minutes is not NULL. A recorded zero is a non-null value and is counted. COUNT(DISTINCT asset_id) instead asks how many different non-null asset IDs occur.

Keeping both visit count and measured-duration count exposes coverage. A total derived from a small measured subset can look authoritative if the missing observations are omitted from the report.

The name inside COUNT changes the question being answered.

SUM and AVG operate on known numeric values in our schema. AVG’s denominator is the number of non-null values, not the number of source rows. MIN and MAX return the extremes of the known values.

These functions describe what is present. They do not estimate the missing duration or correct a biased collection process. If the longest visits are the ones technicians forget to enter, a known-only average can still mislead.

Report the population and coverage with the result.

CREATE TABLE assets (
  asset_id TEXT PRIMARY KEY NOT NULL,
  site TEXT NOT NULL,
  kind TEXT NOT NULL,
  active INTEGER NOT NULL CHECK (active IN (0, 1))
);
CREATE TABLE inspections (
  inspection_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  minutes INTEGER CHECK (minutes >= 0),
  finding TEXT
);
CREATE TABLE readings (
  reading_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  temperature REAL
);
INSERT INTO assets VALUES
 ('P1','north','pump',1), ('P2','north','fan',1),
 ('P3','south','pump',0), ('P4','south','pump',1);
INSERT INTO inspections VALUES
 (101,'P1',30,'loose'), (102,'P1',NULL,NULL),
 (103,'P2',0,'clear'), (104,'P3',20,'clear'),
 (105,'P2',10,'loose'), (106,'P1',15,'loose');
INSERT INTO readings VALUES
 (1,'P1',70.0), (2,'P1',80.0), (3,'P2',NULL), (4,'P3',65.0);

SELECT COUNT(DISTINCT asset_id) AS visited_assets,
       MIN(minutes) AS shortest, MAX(minutes) AS longest
FROM inspections;

An aggregate query without GROUP BY still returns one row when no source rows qualify. COUNT returns zero; SUM and AVG return NULL when no non-null values exist. An all-null selection has the same missing numeric aggregate, although its row count can be positive.

SQLite also has TOTAL, which returns a floating-point zero for no non-null inputs. It is not a portable substitute with the same meaning as SUM. COALESCE(SUM(...),0) is an explicit policy choice: it may be suitable for no expenditure, but it can conceal unrecorded work.

Zero work and no known measurement should be distinguished when the report depends on that difference.

WHERE determines which visits reach the aggregate. Summing durations after finding = loose answers a different question from summing all visits and labelling the total loose afterwards.

The workshop makes this ordering visible. The comparison emits a smaller record list, and the sum component consumes that list. Try the all-missing loose visits case to see why a numeric zero is not an acceptable replacement under the stated rule.

The filter defines the report population; the aggregate describes that population.

A duration total and an average both use minutes, while counts use records or assets. Name columns so a downstream reader can distinguish them. Rounding is a display decision; preserve sufficient precision for later calculations.

For money or quantities requiring exact decimal arithmetic, consider the database’s numeric types and units carefully. Our integer minutes avoid that issue for sums, while averages can be fractional.

A correct formula with the wrong unit still answers the wrong question.