Common mistake

SQL Percent Change From Zero: Infinity and NaN

A zero in the earlier period makes percent change divide by zero. DuckDB returns Infinity or NaN without an error. Guard with NULLIF and label the rows.

The symptom

You compare each category's May orders with April's and compute the percent change. Most rows look fine. Two read Infinity, two read NaN, and when you sort by growth, the categories with no orders at all sit at the top of the report.

Why it happens

Percent change divides by the earlier period: (may - april) / april. When April is 0, that's a division by zero. DuckDB follows floating-point rules here rather than raising an error: a positive number divided by zero is Infinity, and zero divided by zero is NaN ("not a number"). So Outdoor Decor, which went from 0 orders to 2, shows Infinity, and Fertilizer, with none in either month, shows NaN.

The values don't stay in their rows. In DuckDB, NaN sorts above every other number, and avg(pct_change) over the column is NaN, so one quiet category spoils the total.

The fix

nullif(april_orders, 0) turns a zero denominator into NULL, and anything divided by NULL is NULL, which aggregates skip and readers understand as "no figure". Then say why there's no figure, since "new this month" and "no orders at all" are different stories.

Wrong: divides by zero

with monthly as (
  select c.category_name,
         count(distinct case when o.order_date >= date '2024-04-01'
                              and o.order_date <  date '2024-05-01'
                             then o.order_id end) as april_orders,
         count(distinct case when o.order_date >= date '2024-05-01'
                              and o.order_date <  date '2024-06-01'
                             then o.order_id end) as may_orders
  from categories as c
  join products as p on p.category_id = c.category_id
  join order_items as oi on oi.product_id = p.product_id
  join orders as o on o.order_id = oi.order_id
  group by c.category_name
)
select category_name,
       april_orders,
       may_orders,
       round(100.0 * (may_orders - april_orders) / april_orders, 1) as pct_change
from monthly
order by category_name

Right: NULLIF and a label

with monthly as (
  select c.category_name,
         count(distinct case when o.order_date >= date '2024-04-01'
                              and o.order_date <  date '2024-05-01'
                             then o.order_id end) as april_orders,
         count(distinct case when o.order_date >= date '2024-05-01'
                              and o.order_date <  date '2024-06-01'
                             then o.order_id end) as may_orders
  from categories as c
  join products as p on p.category_id = c.category_id
  join order_items as oi on oi.product_id = p.product_id
  join orders as o on o.order_id = oi.order_id
  group by c.category_name
)
select category_name,
       april_orders,
       may_orders,
       round(100.0 * (may_orders - april_orders)
             / nullif(april_orders, 0), 1) as pct_change,
       case
         when april_orders = 0 and may_orders = 0 then 'no orders'
         when april_orders = 0 then 'new in May'
       end as note
from monthly
order by category_name

The four categories with real April orders keep their numbers. The other four get NULL and a note, and the report sorts and averages cleanly.

Other engines fail differently

PostgreSQL and SQL Server stop the query with a division-by-zero error, so you find out at once. MySQL and SQLite return NULL, which looks like the fixed query without the label. DuckDB's Infinity and NaN are the quietest of the three: the query succeeds and the numbers look like data. nullif works the same way in all of them.