# How to Handle Divide by Zero In SQL

Database:

Operators:

Table of Contents

## Problem:

You want to perform division in your SQL query, but the denominator is an expression that can be zero. The database will give you an error when the denominator is in fact zero.

## Example:

Our database has a table named

with data in the following columns: **investor_data**`id`

, `investor_year`

, `price_per_share`

, `income`

, and `expenses`

.

id | investor_year | price_per_share | income | expenses |
---|---|---|---|---|

1 | 2016 | 20 | 3200 | 2300 |

2 | 2017 | 130 | 2000 | 2000 |

3 | 2018 | 40 | 200 | 100 |

4 | 2019 | 15 | 5900 | 4900 |

Let’s divide the price per share by the difference between income and expenses to determine the P/E ratio (price-earnings ratio) for each year. Notice that there are cases when income equals expenses, so their difference (the earnings) will be zero. Thus, we need to find a way to avoid division by zero.

## Solution 1: Use NULLIF function

SELECT investor_year, price_per_share/NULLIF(income-expenses, 0) AS P_E_ratio FROM investor data;

This query returns the P/E ratio for each year as described in the problem statement:

investor_year | P_E_ratio |
---|---|

2016 | 0.0222 |

2017 | NULL |

2018 | 0.4000 |

2019 | 0.0150 |

If the difference between income and expenses is zero (as is the case for the year 2017), the `NULLIF`

function changes the zero to a `NULL`

value. Therefore, division by zero gives you `NULL`

in the result.

## Discussion:

If you’d like to handle division by zero gracefully, you can use the `NULLIF`

function. `NULLIF`

takes two arguments: the expression of interest, and the value you want to override. If the first argument is equal to the second, then `NULLIF`

returns `NULL`

; otherwise, it returns the first argument.

You can use this function to handle a potential division by zero by wrapping the denominator in a call to `NULLIF`

. In our example if the difference between income and expenses is zero, this value will be changed to `NULL`

, and the denominator in the division will be `NULL`

, not zero.

## Solution 2: Use WHERE

Of course, in some situations, you can use a simpler solution: Just avoid division by zero by using `WHERE`

with the comparison operator `<>`

. In our example, we could check if income-expenses is different than 0. If it is, the calculation will be returned.

SELECT investor_year, price_per_share/(income-expenses) AS P_E_ratio FROM investor data WHERE (income-expenses) <> 0 ;

Note that with the `WHERE`

solution, you’ll get fewer rows than there are in the

table. The rows where income and expenses are equal will not be shown in the final result.**investor_date**

investor_year | P_E_ratio |
---|---|

2016 | 0.0222 |

2018 | 0.4000 |

2019 | 0.0150 |