NULLIF, numeric division & ROUND

Calculate an average without inventing data

Lesson 13 of 20 · 12 min · Module 5: Explain a more complex report

An empty group does not have the same meaning as a measured zero. Keep the distinction visible when calculating ratios.

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Distinguish an empty measurement from zero
Choose safe numeric division

The idea

NULLIF(value, 0) turns a zero denominator into NULL. Multiplying the numerator by 1.0 requests non-integer arithmetic in SQLite. ROUND(value, 2) controls the displayed precision.

SELECT ROUND(8 * 1.0 / NULLIF(3, 0), 2);

01Define the denominator

An enrollment is one observation. COUNT(e.id) counts matching enrollments while avoiding the placeholder row retained by a left join.

02Keep the arithmetic intentional

SUM and COUNT use integer values here. Multiplying by 1.0 makes the division produce a non-integer value before rounding.

SELECT ROUND(7 * 1.0 / 2, 2);

Output

3.5

03Preserve missing meaning

No enrollments means there is no measured average. Leaving NULL communicates that absence; replacing it with zero would tell a different story.

Your turn

For every course, return title and average completed lessons per enrollment, rounded to 2 decimal places. Use courses LEFT JOIN enrollments. Calculate SUM(completed) * 1.0 / NULLIF(COUNT(e.id), 0). Leave the average NULL for a course with no enrollments. Sort by course id.

What the checks look for

Reports precise averages and a NULL for the empty course

Try it in the editor →

Take this with you

A report should preserve the meaning of missing observations.

Go a little further

Add an enrollment-count column so a reader can judge how many observations support each average.