SQL percentage change when the previous value is zero

Percentage change is (current - previous) / previous × 100. When the previous value is zero, the ordinary relative-change formula is undefined. A SQL query should preserve that distinction instead of replacing it with a convenient growth percentage.

If sales move from zero to 50 units, you can report an increase of 50 units or “new activity from a zero baseline.” Reporting 100% growth would imply a baseline that doubled, which did not happen.

This lesson uses a self-contained example you can execute with the commerce lab's SQLite runner. The values are invented to expose boundary cases, not to describe business performance.

Make the exceptional cases visible

WITH comparisons(label, previous_value, current_value) AS (
    VALUES
      ('ordinary growth', 100.0, 120.0),
      ('ordinary decline', 100.0, 80.0),
      ('new activity', 0.0, 50.0),
      ('still zero', 0.0, 0.0),
      ('missing baseline', NULL, 50.0)
)
SELECT
    label,
    current_value - previous_value AS absolute_change,
    100.0 * (current_value - previous_value)
        / NULLIF(previous_value, 0) AS percentage_change
FROM comparisons;

The ordinary growth and decline values are 20% and -20%. The zero-baseline and missing-baseline cases produce NULL percentages. The absolute change remains 50 for new activity and zero for still-zero activity; it is unknown when the previous value is unknown.

NULLIF(previous_value, 0) turns a zero denominator into NULL. This is an explicit undefined-result policy, not an attempt to hide an error. The application should display the reason alongside the missing percentage. SQLite expression reference.

Add an interpretation label

WITH comparisons(label, previous_value, current_value) AS (
    VALUES
      ('ordinary growth', 100.0, 120.0),
      ('ordinary decline', 100.0, 80.0),
      ('new activity', 0.0, 50.0),
      ('still zero', 0.0, 0.0),
      ('missing baseline', NULL, 50.0)
)
SELECT label,
    CASE
        WHEN previous_value IS NULL OR current_value IS NULL
            THEN 'Insufficient observed values'
        WHEN previous_value = 0 AND current_value = 0
            THEN 'No activity in either period'
        WHEN previous_value = 0
            THEN 'Change from a zero baseline'
        ELSE 'Relative change available'
    END AS comparison_status
FROM comparisons;

The label distinguishes missing data from a known zero. Those cases require different actions: missing data may need investigation, while a new product or channel can legitimately have no previous activity.

You can choose to display a zero-to-zero case as “no change,” but avoid suggesting that the relative formula produced 0%. Keep the raw result and the display convention separate so downstream calculations do not treat an undefined percentage as an ordinary numeric observation.

Negative baselines need a business interpretation

If a metric moves from -100 to -50, the ordinary formula returns -50%, even though the value increased toward zero. For quantities such as signed profit or cash flow, a relative percentage can be difficult to interpret.

Do not silently replace the denominator with its absolute value. That creates a different formula and requires an explicit convention. Often an absolute change, the two endpoint values and a plain-language explanation are clearer.

For inherently nonnegative measures such as order counts, a negative baseline should instead trigger a data-quality check. The meaning of the metric decides whether a negative value is legitimate.

Protect the period comparison

Even a valid denominator does not make two periods comparable. Check whether both cover the same number of days, use the same eligibility rules and have similar source completeness.

A month-to-date value on day five should not be compared with an entire previous month without clear labelling. A new region entering the dataset can change aggregate growth even if every existing region is flat. A delayed source extract can look like a business decline.

Keep the comparison period, extraction cutoff and population definition with the result. If the comparison is partial, say so before interpreting the percentage.

Do not average percentage changes blindly

Suppose two regions have different baseline sizes. Averaging their percentage growth equally does not generally produce the overall growth rate. Aggregate comparable current and previous values first, then calculate the overall ratio.

That is the same underlying issue as averaging regional order values without weights: the denominator carries information that a standalone percentage has lost.

Zero-baseline segments make the problem more obvious. An overall business can have a valid previous total even while one new segment has an undefined individual growth rate. Calculate each measure at its intended population rather than forcing every segment into a numeric percentage.

A useful reporting contract

Store current value, previous value, absolute change, relative change and comparison status. Define the display convention for zero and missing baselines, then test the normal, zero, negative and missing cases.

Exercise: add a comparison from 50 to zero. Its relative change is -100%, which is defined because the previous value is nonzero. Explain why the reverse move from zero to 50 cannot be called +100% under the same formula.

NeuraPath's Data Analytics with Generative AI course combines SQL, statistics and reporting. These distinctions help you review both dashboard calculations and AI-written performance summaries before a confident but undefined growth claim reaches a decision-maker.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Calculate return rates with the right denominator.
• Continue with Find duplicate business keys without deleting evidence.