SQL NULLIF: division by zero and the meaning of a missing result

You have a count of completed tasks and a count of all tasks. You want a proportion. Three out of eight sounds straightforward until the query returns zero. Then you encounter a row with no denominator. Adding a default value can make the report look tidy while leaving it answering the wrong question.

I separate three decisions here: when the metric can be calculated, what type the division needs, and what to display when there is no result. NULLIF helps with one of them. It can also clean an agreed missing-value marker, provided you know exactly what is being compared.

I checked every SQL block and result table below in SQLite 3.53.1 through node:sqlite in Node 24.18.0. Run them in order in one fresh test connection. Notes about other engines come from their documentation; I do not claim to have executed these examples in PostgreSQL, SQL Server or MySQL.

Two arguments and one result

NULLIF(a, b) returns NULL if the comparison a = b is true. Otherwise, it returns the first argument. Order matters: when the numbers differ, the value on the left survives. The SQLite documentation describes the rule.

-- example: basics
SELECT NULLIF(7, 7) AS equal_values,
       NULLIF(7, 2) AS different_values,
       NULLIF(7, NULL) AS missing_second,
       NULLIF(NULL, 7) AS missing_first,
       NULLIF(NULL, NULL) AS both_missing;

The second column contains seven, not five. The function subtracts nothing. The third also contains seven because comparing it with a missing second argument does not produce true. If the first argument is already NULL, the result remains NULL.

A missing result therefore does not prove equality. It may come from equal arguments or from a missing first value. Two missing observations do not become two confirmed equal measurements either. When I need to distinguish the reasons, I add a separate status.

A small dataset for the calculations

These data are synthetic. solved counts completed tasks, total counts all tasks, and previous_total is the previous total. The note column will support a text-cleaning example later. Each row represents one separate sample in the report.

-- example: fixture
CREATE TEMP TABLE samples (
  id INTEGER PRIMARY KEY,
  solved INTEGER,
  total INTEGER,
  previous_total INTEGER,
  note TEXT
);
INSERT INTO samples VALUES
  (1, 3, 8, 8, ' ok '),
  (2, 0, 5, 4, ''),
  (3, 0, 0, 0, '   '),
  (4, 2, NULL, 6, NULL),
  (5, NULL, 4, 4, 'N/A'),
  (6, 2, 4, NULL, 'n/a');

Sample two has a valid count of zero completed tasks. Sample three has no tasks in its denominator. Sample four has an unknown total, and sample five has no numerator. In sample six, the current measurement exists, but the previous one is missing.

I create a temporary table so you can experiment in a separate connection. The queries below do not update it. Putting NULLIF in a SELECT result does not delete the original zero or text from the stored table.

Denominators and integer division

NULLIF(total, 0) turns a zero denominator into NULL. A positive denominator survives unchanged, while a missing one stays missing. Now compare the two result columns:

-- example: ratios
SELECT id,
       solved / NULLIF(total, 0) AS integer_ratio,
       1.0 * solved / NULLIF(total, 0) AS ratio
FROM samples
ORDER BY id;

The first division uses two integers in SQLite and discards the fractional part. Three eighths becomes zero, and so does two quarters. In the second column, I multiply the numerator by 1.0 before division, so the operation preserves a fractional result. The SQLite expression documentation describes its arithmetic rules.

The zero in sample two means something different from the zeros caused by the wrong arithmetic type. Here, genuinely zero out of five tasks were completed. Samples three, four and five do not receive numeric ratios. I do not automatically turn them into zeros, because that would conceal three different situations.

Changing the type after division cannot restore the fraction that was already discarded. This small query shows that alongside SQLite’s handling of a zero denominator:

-- example: division
SELECT 100 / 0 AS direct_division,
       100 / NULLIF(0, 0) AS guarded_division,
       (3 / 8) * 1.0 AS cast_too_late,
       3.0 / 8 AS fractional_division;

SQLite also returns NULL for direct division by zero. Saying that SQL always raises an error would be incorrect. MySQL also documents a NULL result for this division. PostgreSQL documents division-by-zero errors, while in SQL Server, session settings such as ANSI_WARNINGS affect the behavior.

I would not change database error settings just to hide a reporting problem. An explicit denominator rule is easier to understand. Also remember that 1.0 uses floating-point arithmetic in SQLite. When calculations require a particular precision, choose the appropriate type and rounding in your database.

NULLIF neither subtracts values nor removes duplicate rows

I will compare the current task total with the previous one. Alongside NULLIF, I show an equivalent CASE selection and a separate subtraction:

-- example: comparison
SELECT id,
       NULLIF(total, previous_total) AS current_if_not_equal,
       CASE WHEN total = previous_total THEN NULL ELSE total END
         AS case_result,
       total - previous_total AS difference
FROM samples
ORDER BY id;

In sample two, NULLIF returns five, while the difference is one. An alias suggesting a difference would be misleading. In sample six, the function returns four even though the previous measurement is unknown. That result does not prove a change between two complete observations.

The CASE expression exposes the selection rule: return a missing value only when equality is confirmed; otherwise, keep the current value. Microsoft describes this relationship. For more detailed classification, I use CASE WHEN.

There are still six rows in the output. The function does not search for duplicate records or deduplicate the table. When comparing columns, name the result so readers know whether it contains a selected value, a difference, or a comparison status.

Turning agreed markers into missing values

Suppose an empty note, a note containing only ordinary spaces, and the exact text N/A all mean missing. Other text must stay. Here is that specific rule:

-- example: clean
SELECT id,
       NULLIF(NULLIF(TRIM(note), ''), 'N/A') AS cleaned_note
FROM samples
ORDER BY id;

First, TRIM removes ordinary spaces from the ends. The inner NULLIF turns empty text into a missing value, and the outer one does the same for N/A. Lowercase n/a remains because this SQLite table uses the default BINARY text comparison. The function documentation explains how comparison rules affect NULLIF.

This is an assumption for this field, not a universal cleaning recipe. If lowercase variants also mean missing, add that rule deliberately. If zero is a valid measurement, do not remove it with NULLIF(score, 0) merely because it looks unimpressive on a chart.

What missing ratios do to an average

Once a ratio becomes NULL, aggregates handle it according to their rules. Three samples have calculated ratios in our dataset: 0.375, zero and 0.5. Their average is approximately 0.292. Replacing the other three results with zeros reduces it to approximately 0.146.

-- example: summary
WITH rates AS (
  SELECT 1.0 * solved / NULLIF(total, 0) AS ratio
  FROM samples
)
SELECT COUNT(*) AS all_samples,
       COUNT(ratio) AS computed_samples,
       ROUND(AVG(ratio), 3) AS mean_ratio,
       ROUND(AVG(COALESCE(ratio, 0)), 3) AS zero_filled_mean
FROM rates;

Both averages can be calculated, but they represent different assumptions. That is why I include both the total sample count and the count of calculated ratios. The SQLite aggregate documentation defines AVG and COUNT. Rounding here only controls how the result is presented.

This is an average of the individual sample ratios. It is not automatically the proportion of completed tasks across the entire collection. For an overall proportion, establish which numerators and denominators belong to the same population. I would not casually add a numerator from a row whose denominator is unknown.

The SQL COALESCE entry covers default values before and after aggregation in more detail.

Exercise: a ratio with an explicit status

Prepare a report that keeps every sample. A missing number should produce missing_data. A nonpositive total should produce invalid_total. A negative completed count, or one larger than the total, should produce invalid_solved. Only the remaining samples should have status ok and a numeric ratio.

Try expressing these conditions, then compare the solution:

-- example: exercise
WITH classified AS (
  SELECT id, solved, total,
         CASE
           WHEN solved IS NULL OR total IS NULL THEN 'missing_data'
           WHEN total <= 0 THEN 'invalid_total'
           WHEN solved < 0 OR solved > total THEN 'invalid_solved'
           ELSE 'ok'
         END AS status
  FROM samples
)
SELECT id, status,
       CASE WHEN status = 'ok'
            THEN 1.0 * solved / NULLIF(total, 0)
       END AS ratio
FROM classified
ORDER BY id;

The condition order determines which reason appears if a row has several problems. Zero completed tasks with a positive denominator passes correctly. NULLIF(total, 0) alone would not reject a negative denominator because it differs from zero, so validation needs its own condition.

Finally, change one sample to have a negative denominator and another to have more completed tasks than total tasks. Check their statuses and missing ratios. Then remove the rows in your test connection and inspect the aggregate counts. That gives you a quick check that the report distinguishes a valid zero result, an invalid measurement, and no input to calculate from.