±SUM() and AVG()

Numeric folds, watch type and NULLs

SUM and AVG skip NULL inputs: a missing measurement is not the same as zero unless you impute it explicitly. For currency and titers, prefer exact numeric types (NUMERIC / DECIMAL) and controlled casts; mixing integers and binary floats invites rounding surprises in long pipelines.

SQL

Warehouse rollups

SELECT warehouse_id,
       SUM(units_on_hand) AS units,
       AVG(days_since_count)::numeric(6,2) AS avg_age
FROM inventory_snapshots
GROUP BY warehouse_id;

✓Illustrative result set

warehouse_id	units	avg_age
W-EAST	184000	4.12
W-WEST	162500	5.88

SQL

AVG ignores NULLs (denominator shrinks)

SELECT AVG(score) AS avg_score
FROM (VALUES (10.0), (NULL), (20.0)) AS t(score);

✓Illustrative result set

avg_score
15

SUM(revenue) after joining to tags

Many-to-many explosion double-counts every dollar. The query “runs” and numbers look plausible until reconciliation.

Sum at invoice grain first

Group revenue in a subquery keyed by invoice id, then join dimensions for reporting, never rely on DISTINCT to paper over duplicate economic events.

⚖️AVG vs “average with zeros”

If NULL means “not run,” AVG is correct; if NULL should count as zero failures, say so in SQL with COALESCE, and document the policy.

Clinical and QC metrics often encode “missing” as NULL on purpose so pass rates are not diluted. Operations dashboards sometimes want zeros, align with the statistic owner before changing the query.

⚡ Advanced / dialect note

Integer SUM on some engines can overflow silently or error depending on mode; promote wide money and count aggregates to a wider type before rolling up fiscal years.

Rolling your own weighted average

Weighted means are SUM(x * w) / SUM(w) with a NULL-safe policy for weights, not a second AVG call on pre-multiplied rows unless weights are identical within each bucket.

TL;DR

SUM adds numeric values; AVG is SUM / COUNT of non-NULL inputs (division by zero becomes NULL in standard SQL). Both ignore NULL rows in the aggregate, do not confuse that with “treat NULL as zero” unless you explicitly COALESCE. Integer averages may truncate unless you cast to a decimal type.