You need to avoid SQL divide by zero errors.
Suppose you need to calculate velocity using the formula v = s / t
Where v = velocity, s = distance, and t = time. Here is the sample data we’ll use.
select 100 s, 10 t union all select 110 s, 11 t union all select 2 s, 0 t union all select 10 s, 1 t union all select 120 s, 20 t
Line Three is going to cause us issues, it will throw an SQL divide-by-zero error.
In the following section we’ll walk through two solutions that take different approaches but solve the sql divide by zero problem the same way.
Solution #1 – Use CASE
Use a case statement to test for a potential divide by zero situation and if you going to have one, then return a result that makes sense for the situation.
In our example, if the time (t) is zero, it results in a SQL divide by zero error, we then return NULL, as the velocity is “undefined.” This solution works with MS SQL Server, PostgreSQL, and MySQL:
select s, t, case when t = 0 then null else s/t end v from ( select 100 s, 10 t union all select 110 s, 11 t union all select 2 s, 0 t union all select 10 s, 1 t union all select 120 s, 20 t ) d
Solution #2 – Use NullIF
The second solution uses NULLIF() to return a NULL when t is null. It is a more compact form of Solution #1, and it also work with all the databases we cover!
select s, t, s / nullif(t,0) v -- null t when 0, to return null on v from ( select 100 s, 10 t union all select 110 s, 11 t union all select 2 s, 0 t union all select 10 s, 1 t union all select 120 s, 20 t ) d
SQL Divide By Zero Discussion
For our example I would prefer to use solution #2 as it is more compact. Some may find a bit harder to read and understand, especially we really null out time (t), and then rely on this to null the entire expression. Beginners may not get this. A comment may help to you and them to remember this detail!
In general, the CASE WHEN example provides more flexibility to return varying values whether the divisor is zero or null.
Dig Deeper: SQL CASE Statement – Simple and Searched Forms