SQL, data modeling, data quality, EDA, visualization · insight-0041

Expect SUM over an empty or all-NULL group to return NULL, and handle it at the edge

Situation
A dashboard shows blank revenue for a route-day with no sales, and a downstream ratio or growth calculation fails or disappears.
Action
Keep NULL in the base aggregate, and convert to zero at the reporting layer only where "no rows" means zero sales. Left join a calendar or route list so empty periods exist as rows before you aggregate.
Reason
Standard SUM returns NULL when there are no non-NULL inputs; missing groups do not appear at all unless a dimension table supplies them.
Boundary
Daily KPIs, sparse products, new routes and any time series built with GROUP BY.

Symptom or risk

NULL propagates through arithmetic, so totals, growth rates and charts silently drop the period instead of showing zero.

When the period truly has no data because collection failed, zero is wrong; show a gap and flag it.

Densifying with a calendar increases row counts and storage.

Using engine-specific helpers such as SQLite's total() in portable code.

sum() returns NULL when there are no non-NULL input rows (the SQL-standard behaviour), while SQLite's non-standard total() returns 0.0.
Built-in Aggregate Functions (SQLite)