SQL NULLIF: Handle Zero Denominators Explicitly

The practice task

Calculate the completion rate for three fictional training cohorts. One cohort has no enrolments. Show its rate as unknown rather than inventing a zero-percent completion result.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE cohorts(name TEXT, enrolled INTEGER, completed INTEGER);
INSERT INTO cohorts VALUES ('Autumn',8,6),('Spring',0,0),('Summer',10,5);

SELECT name, ROUND(100.0 * completed / NULLIF(enrolled, 0), 1) AS completion_pct
FROM cohorts ORDER BY name;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The zero in Spring becomes NULL, so the rate remains NULL. Autumn produces 75.0 and Summer produces 50.0. This preserves the difference between a measured low completion rate and a cohort for which a rate cannot be calculated.

The constant 100.0 keeps the calculation in floating-point arithmetic in this SQLite example. The placement of ROUND controls the displayed result after division. Rounding an intermediate fraction first could change the final percentage.

NULLIF is not a substitute for checking the data model. Negative enrolments or more completions than enrolments are separate validation failures. If the business needs a label for an unstarted cohort, derive that label from the original enrolled count rather than treating all NULL rates as the same event.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Do not wrap the result in COALESCE(..., 0) unless zero is the intended meaning.
• NULL equality is not the same as ordinary value equality.
• State the rate denominator so readers know what the percentage measures.