AirOps | SQL Guide • How to Avoid Dividing by Zero in SQL (free SQL tool)

Division by zero is a common problem when working with data in SQL. It can lead to unexpected results and can even cause your query to fail.

The best way to avoid dividing by zero in SQL is to use the NULLIF function. This function takes two arguments, and returns NULL if the two arguments are equal. For example, if you wanted to divide two columns, a and b, you would use the following query:

SELECT a / NULLIF(b, 0) FROM table_name;

This query will return the result of a / b, unless b is equal to zero. In that case, it will return NULL instead.

Examples

Let's look at a few examples of how this function can be used. Suppose you have a table called sales with the following data:

transactions
product	quantity	price
Widget	10	10.00
Gizmo	0	5.00
Thingamabob	5	2.00

If you wanted to calculate the total revenue for each product, you would use the following query:

SELECT product, quantity * NULLIF(price, 0) AS revenue FROM sales;

This query would return the following results:

purchases
product	revenue
Widget	100.00
Gizmo	NULL
Thingamabob	10.00

‍
As you can see, the query correctly returns NULL for the Gizmo product, since its price is equal to zero.

Additional Info

The NULLIF function is supported by most major databases, including MySQL, PostgreSQL, and SQL Server. However, the syntax may vary slightly depending on the database you are using. For more information, check out the documentation for your particular database. 🤓

How to Avoid Dividing by Zero in SQL

Examples

Additional Info

Want to build your own LLM Apps with AirOps👇👇