A Friendly Reminder: One Plus NULL Equals NULL

With the unveiling of Fivetran Transformations, you now have even more reason to perform arithmetic operations across columns within your data warehouse. Unfortunately, a case may arise where a value is missing, but its absence should not invalidate an entire record.

Suppose you have sales records from a variety of jurisdictions and product categories. Not all jurisdictions have sales taxes and some product categories come with mandatory hazardous materials fees:

item	price	tax	hazmat_fee
oat_bran	1.00	0.06	NULL
scalpel	5.50	NULL	NULL
mercury_fulminate	200.00	NULL	35.00
cesium_iodide	450.00	45.00	35.00

If you tried to calculate the total cost for each record using the following query:

SELECT item, price, tax, hazmat_fee, price + tax + hazmat_fee as total_cost FROM sales.records

Your results will look like so:

item	price	tax	hazmat_fee	total_cost
oat_bran	1.00	0.06	NULL	NULL
scalpel	5.50	NULL	NULL	NULL
mercury_fulminate	200.00	NULL	35.00	NULL
cesium_iodide	450.00	45.00	35.00	530.00

What you really want in this case is to default to a value of 0 whenever a NULL is encountered. You can do this using the COALESCE function, which takes the first non-NULL argument from a list):

SELECT item, price, tax, hazmat_fee, price + COALESCE(tax, 0) + COALESCE(hazmat_fee, 0) as total_cost FROM sales.records

Returning:

item	price	tax	hazmat_fee	total_cost
oat_bran	1.00	0.06	NULL	NULL
scalpel	5.50	NULL	NULL	NULL
mercury_fulminate	200.00	NULL	35.00	NULL
cesium_iodide	450.00	45.00	35.00	530.00

Remember - when adding across columns, one plus NULL equals NULL! This is true for subtraction, multiplication, and division, too.

Learn more about Fivetran Transformations with a personalized demo or a free trial.

Start for free