MySQL - Top 3 Techniques to Handle and Prevent Division By Zero Error
When performing data calculations for ratio in MySQL, it's important to consider division by zero error to avoid runtime error. We'll review 3 MySQL techniques in this post to handle the situation of denominator having zero value due to late arriving data or logging error.
Example Data
We have a table named ConversionTracking which has three columns.
Column log_date records the log date of the conversion data.
Column purchases records the total number of purchases for a specific date.
Column ad_clicks records the total number of advertisement clicks for a specific date.
We would like to calculate daily conversion rate using a simple formula purchases / ad_clicks.
CREATE TABLE ConversionTracking
(
log_date varchar(300),
purchases varchar(300),
ad_clicks varchar(300)
);
INSERT INTO ConversionTracking (log_date,purchases,ad_clicks) VALUES ('2022-01-01', '3', '120');
INSERT INTO ConversionTracking (log_date,purchases,ad_clicks) VALUES ('2022-01-02', '5', '200');
INSERT INTO ConversionTracking (log_date,purchases,ad_clicks) VALUES ('2022-01-03', '7', '0');
INSERT INTO ConversionTracking (log_date,purchases,ad_clicks) VALUES ('2022-01-04', '0', '0');
INSERT INTO ConversionTracking (log_date,purchases,ad_clicks) VALUES ('2022-01-05', '10', '250');
Use
DB-Fiddle to execute SQL scripts on sample data.
| log_date | purchases | ad_clicks |
|---|---|---|
| 2022-01-01 | 3 | 120 |
| 2022-01-02 | 5 | 200 |
| 2022-01-03 | 7 | 0 |
| 2022-01-04 | 0 | 0 |
| 2022-01-05 | 10 | 250 |
Solution 1: Bult-in NULLIF Function
This solution uses the built-in NULLIF() function which takes two parameters. When the value of the first parameter is equal to the second parameter, the function will NULL. The result of division will be NULL if the denominator has a NULL value. The code is now protected from encountering division by zero error.
SELECT log_date
,purchases / NULLIF(ad_clicks, 0) AS conversion_rate
FROM ConversionTracking
ORDER BY log_date;
| log_date | conversion_rate |
|---|---|
| 2022-01-01 | 0.025 |
| 2022-01-02 | 0.025 |
| 2022-01-03 | null |
| 2022-01-04 | null |
| 2022-01-05 | 0.04 |
Solution 2: CASE Statement
This solution uses the CASE statement to check the value of the denominator, ad_clicks.
If the denominator has a value of 0, NULL is returned to prevent division by zero error. Otherwise, proceed with division as usual.
SELECT log_date
,CASE ad_clicks
WHEN 0
THEN NULL
ELSE purchases / ad_clicks
END AS conversion_rate
FROM ConversionTracking
ORDER BY log_date;
| log_date | conversion_rate |
|---|---|
| 2022-01-01 | 0.025 |
| 2022-01-02 | 0.025 |
| 2022-01-03 | null |
| 2022-01-04 | null |
| 2022-01-05 | 0.04 |
Solution 3: WHERE Clause
This solution uses the WHERE condition to filter out the rows where ad_clicks is zero to avoid division by zero error.
SELECT log_date
,purchases / ad_clicks AS conversion_rate
FROM ConversionTracking
WHERE ad_clicks > 0
ORDER BY log_date;
| log_date | conversion_rate |
|---|---|
| 2022-01-01 | 0.025 |
| 2022-01-02 | 0.025 |
| 2022-01-05 | 0.04 |
Conclusion
It is important to properly handle division by zero error when calculating ratio in our SQL scripts. I prefer solutions using NULLIF function or CASE statement as we'll be alerted with NULL value in the calculated ratio column. The solution using WHERE clause is OK, but we might miss catching critical logging errors that should be resolved as soon as possible.