Numbers and missing values
Part 1 of 2
Repair integer division before rounding
When both operands are integers, SQLite division truncates toward zero. ROUND(7 / 2, 1) rounds an already truncated 3; it cannot recover the missing fraction. Put a real-valued operand into the division first, such as 7 * 1.0 / 2.
A rate needs a named numerator and denominator. A mean number of scans per package is different from the percentage of packages delivered. Neither a decimal place nor an attractive alias establishes the business meaning.
NULLIF(denominator, 0) changes a zero denominator into NULL. This leaves an undefined ratio missing rather than labeling it zero. Explain why the denominator is valid before interpreting the result.
Worked example
SELECT 7 / 2 AS integer_result, ROUND(7 * 1.0 / 2, 1) AS real_result, 7.0 / NULLIF(0, 0) AS undefined_ratio;
The first expression demonstrates truncation; the second preserves the fractional result. A missing ratio communicates an undefined operation, not a successful zero rate.
The fixed denominator is justified only by this snapshot. Recheck SELECT COUNT(*) FROM packages before reusing the calculation with new data.
Part 2 of 2
Keep missing values honest
In packages, a NULL from_address_id means no sender is on file. It does not mean address 0, the destination, or proof that the sender deliberately hid their identity. Use IS NULL to select these records; equality to NULL does not return true.
COALESCE returns the first non-NULL value; IFNULL is the two-argument alternative. For a readable report, 'Sender #' || from_address_id becomes NULL when the sender is missing. Wrap the entire label expression in COALESCE to provide a readable fallback without changing the original ID.
An empty string and an all-space string are not NULL. COALESCE('', 'Unknown') keeps the empty string. For a text-import cleanup rule, NULLIF(TRIM(value), '') can turn an empty cleaned string into NULL before applying COALESCE. Use that rule only when a blank really means missing in your data contract.
Worked example
SELECT COALESCE('', 'Unknown') AS empty_kept, COALESCE(NULLIF(TRIM(' '), ''), 'Unknown') AS blank_label, IFNULL(NULL, 'Unknown') AS null_label;The three expressions distinguish an empty string, spaces transformed into NULL, and an existing NULL. They need different tests.
The practice leaves the relationship column intact. A readable fallback must not invent a relationship to address 0.