How to Avoid Dividing by Zero in MySQL

MySQL is an open-source relational database management system in short RDBMS. We use it to store, retrieve, and manipulate data very efficiently. In MySQL “Divide by zero error” is encountered when we try to divide one column with another column that contains some zero values.

“Diving by zero error” is a common problem while performing division in most of the data. It can eventually solve some basic step-by-step approaches.

In this article, we are going to demonstrate various methods through which we can tackle “Divide by zero error” very easily. We are going to see various approaches with clear and concise examples along with their explanations.

How to Avoid Dividing by Zero in SQL

MySQL allows us several approaches through which we can ignore dividing by zero error. There are certain built-in functions and some queries along with the where clause, can help us to achieve our goal. Below are some mentioned methods through which we can avoid dividing by zero error.

1. Using the CASE statement

2. Using NULLIF()

3. Using WHERE Clause

Let’s Setup an Environment

We will use the following table to implement our approaches.

Table – geeksforgeeks

To create this table in our database, we have to execute the following MySQL queries:

Create Table name geeksforgeeks:

CREATE TABLE geeksforgeeks(
  user_id int PRIMARY KEY,
  name varchar(100),
  montly_score int,
  total_score int
 );

Insert the value in geeksforgeeks table:

INSERT INTO geeksforgeeks(user_id,name, montly_score , total_score)
VALUES(01,'Vishu',50,150);
INSERT INTO geeksforgeeks(user_id,name, montly_score , total_score)
VALUES(02,'Neeraj',45,125);
INSERT INTO geeksforgeeks(user_id,name, montly_score , total_score)
VALUES(03,'Aayush',0,110);
INSERT INTO geeksforgeeks(user_id,name, montly_score , total_score)
VALUES(04,'Sumit',40,100);
INSERT INTO geeksforgeeks(user_id,name, montly_score , total_score)
VALUES(05,'Vivek',0,95);

After executing the above queries, we can observe a table has been created in our database.

1. Using CASE statement

A CASE statement is a conditional expression used to perform different actions based on the specified conditions.

Syntax:

SELECT 
CASE WHEN column_02 = 0 THEN NULL 
ELSE column_01 / column_02 END 
FROM table_name;

In this example, we will be using case statement to tackle dividing by zero error. We will set values to NULL if we face some zero values in our divisor column otherwise we will continue performing our dividing operations.

Query:

SELECT name,
CASE WHEN montly_score = 0 THEN NULL 
ELSE total_score / montly_score END AS average_score 
FROM geeksforgeeks;

Output:

Using CASE statement

Explanation: In the above image, we can clearly see that the average score column contains some NULL values for Aayush and Vivek. We can see for those rows, monthly score has zero value. Therefore it comes under our defined CASE and display the NULL values for that particular row.

2. Using NULLIF()

In MySQL, NULLIF() operator is used to return a NULL value when our specified expressions are equals. We are going to use it to return NULL values when we encounter zero values in our divisor column.

Syntax:

SELECT column_01 / NULLIF(column_02, 0) 
FROM table_name;

NOTE: If column_02 is 0 for any row then the NULLIF operator will return NULL value.

In this example, we are going to use the NULLIF operator to tackle our diving by zero error. We will return NULL for all those rows which have zero in their monthly score column.

Query:

SELECT name, total_score / NULLIF(montly_score, 0) AS average_score
FROM geeksforgeeks;

Output:

Using NULLIF()

Explanation: Likewise in the previous example, we can see that there are NULL values for Aayush and Vivek. This is because we have used the NULLIF() operator to return NULL if there is zero in the monthly score column. As we have seen those rows have zero values in their monthly score column.

3. Using WHERE Clause

WHERE Clause is used to filter rows based on some conditions. However, WHERE Clause directly cannot prevent dividing by zero error but it can skip those rows.

Syntax:

SELECT column_01 / column_02
FROM table_name
WHERE column_02 != 0;

In this example, we are going to use the WHERE clause to skip those records which contains zero values in its divisor column. We will specify the condition with the help of WHERE clause.

Query:

SELECT name, total_score / montly_score AS average_score
FROM geeksforgeeks
WHERE montly_score != 0;

Output:

using WHERE Clause

Explanation: In the above example, we can see that we have skipped rows for Aayush and Vivek . This is because their monthly score column has zero value. For other records we have performed our dividing operations.

Conclusion

Overall, to avoid dividing by zero in MySQL we can use certain built-in functions or with some queries along with where clause. We have explained different methods such as NULLIF() function, WHERE Clause and CASE statement. We have explained all the methods with clear and concise examples. Now you have good understanding of tackling dividing by zero error. Now you can write queries related to it and can get the desired result.