How to handle divide by zero error in SQL
- How-Tos FAQs
- December 17, 2018
Whenever we perform a division in SQL, we must remember to handle a ‘divide by zero’ error. Even though there is no data with value zero in the denominator, for now, we must handle the ‘divide by zero’ error because there might be data with zero value in the future. We can handle this by replacing zero value with NULL.
How to handle divide by zero error in SQL
Let us consider a table as below:
CREATE TABLE working_groups (
name VARCHAR(50),
number_of_member INT,
budget FlOAT
);
INSERT INTO working_groups
SELECT 'Group 1', 10, '25000' UNION ALL
SELECT 'Group 2', 5, '20000' UNION ALL
SELECT 'Group 3', 3, '27000' UNION ALL
SELECT 'Group 4', 4, '40000' UNION ALL
SELECT 'Group 5', '0', '64000';MySQL
-- Divide by zero handled using NULLIF function
SELECT `name`
, number_of_member
, budget/NULLIF(number_of_member, 0) 'budget per member'
FROM working_groups;
-- Output
# name number_of_member budget per member
----------------------------------------------------------
Group 1 10 2500
Group 2 5 4000
Group 3 3 9000
Group 4 4 10000
Group 5 0 null
-- Divide by zero handled using IF function
SELECT `name`
, number_of_member
, IF(number_of_member = 0 ,'Divide by Zero', budget/number_of_member) 'budget per member'
FROM working_groups;
-- Output
# name number_of_member budget per member
----------------------------------------------------------
Group 1 10 2500
Group 2 5 4000
Group 3 3 9000
Group 4 4 10000
Group 5 0 Divide by ZeroSQL Server
-- Divide by zero handled using NULLIF function
SELECT 'name' name
, number_of_member
, budget/NULLIF(number_of_member, 0) 'budget per member'
FROM working_groups;
-- Output
name number_of_member budget per member
--------------------------------------------------
Group 1 10 2500
Group 2 5 4000
Group 3 3 9000
Group 4 4 10000
Group 5 0 NULL
-- Divide by zero handled using IIF function
SELECT name
, number_of_member
, IIF(number_of_member = 0 ,'Divide by Zero'
,CAST(budget/number_of_member AS VARCHAR(10))
) 'budget per member'
FROM working_groups;
-- Output
name number_of_member budget per member
----------------------------------------------------
Group 1 10 2500
Group 2 5 4000
Group 3 3 9000
Group 4 4 10000
Group 5 0 Divide by Zero