Don't let SQL divide by zero ruin your day | Essential SQL
May 11, 2024
How to Avoid Divide by Zero
In SQL, divide by zero ruins our day. Your whole query fails, and you just want results. What to do?
Watch this video to learn two solutions to work around this issue.
One Last Thing...
This video is part of my Beginner Learning series. If you're interested in learning SQL subscribe to @Essential SQL and then check out our Intermediate Learner Playlist. Of course, I also encourage you to visit https://www.essentialsql.com to learn even more!
Important links:
Sample PizzaDB: https://github.com/kwenzel1/EssentialSQLSamples/tree/master/PizzaDB
Corresponding Article: https://www.essentialsql.com/how-to-avoid-sql-divide-by-zero/
Learn more about derrived tables here: https://www.essentialsql.com/derived-tables/
Learn more about CASE WHEN here: https://www.essentialsql.com/sql-case/
Source Code:
--How to Avoid SQL Divide by Zero
--Works with SQL Server, PostgreSQL, and MySQL
--
--In the following code we’ll walk through two solutions that take different
--approaches but solve the sql divide by zero problem the same way.
--See: https://www.essentialsql.com/how-to-avoid-sql-divide-by-zero/
--sample data
--try to calculate velocity = distance / time:
--where
--v = velocity
--s = distance traveled
--t = time
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
--throw error!
--velocity = s / t
select s, t, s/t 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 1 -- Works with SQL Server, PostgreSQL, MySQL
--Use CASE WHEN
--learn more about derrived tables here: https://www.essentialsql.com/derived-tables/
--learn more about CASE WHEN here: https://www.essentialsql.com/sql-case/
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 -- Works with SQL Server, PostgreSQL, MySQL
--Use NULLIF
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
Show More Show Less