# How to Avoid Dividing by Zero in MySQL

#### Problem:

You’d like to avoid the division-by-zero error.

#### Example:

Our database has a table named `numbers` with data in the columns `id`, `number_a`, and `number_b`.

idnumber_anumber_b
140
257-5
3-756
4-670
52355
6-8-4

Let’s divide `number_a` by `number_b` and show the table with a new column, `divided`, with the result of the division.

#### Solution 1:

```SELECT
*,
number_a / NULLIF(number_b, 0) AS divided
FROM numbers;
```

#### Solution 2:

```SELECT
*,
CASE
WHEN number_b = 0
THEN NULL
ELSE number_a / number_b
END AS divided
FROM numbers;
```

The result is:

idnumber_anumber_bdivided
140NULL
257-5-11.4000
3-756-0.1250
4-670NULL
523550.4182
6-8-42.0000

#### Solution 3:

```SELECT
*,
number_a / number_b AS divided
FROM numbers
WHERE number_b != 0;
```

The result is:

idnumber_anumber_bdivided
257-5-11.4000
3-756-0.1250
523550.4182
6-8-42.0000

#### Discussion:

The first solution uses the `NULLIF()` function, which takes two numbers as arguments. When the first argument is equal to the other argument, the function returns `NULL` as a result. If `number_b` is equal to zero, the divisor is `NULL`, and the result of the division is `NULL`.

The second solution uses the `CASE` statement. If the condition after the `WHEN` keyword is true (in our case, the condition is `number_b = 0`), we specify that NULL be returned. Otherwise, the division happens as usual.

The third solution simply uses the `WHERE` condition to filter out the rows where `number_b` is zero. The rows with `number_b` equal to zero are missing from the result set.