Imagine your manager asks you to prepare a sales report.
You need to calculate revenue per unit for every product.
Your query works fine until you reach a product with zero units sold.
Suddenly, you get a divide-by-zero error.
How would you fix it?
Let's look at the data.
sales
| product_id | product_name | revenue | units_sold |
|---|---|---|---|
| 101 | Laptop | 90000 | 3 |
| 102 | Mouse | 5000 | 10 |
| 103 | Keyboard | 12000 | 0 |
| 104 | Monitor | 30000 | 5 |
Your challenge
You write this query:
SELECT
product_name,
revenue,
units_sold,
revenue / units_sold AS revenue_per_unit
FROM sales;
What happens when SQL tries to calculate revenue per unit for the Keyboard?
A) It returns 0.
B) It returns NULL.
C) It raises a divide-by-zero error.
D) It skips the row.
Think before you answer.
The solution
The trick is to use "NULLIF()".
SELECT
product_name,
revenue,
units_sold,
revenue / NULLIF(units_sold, 0)
AS revenue_per_unit
FROM sales;
Expected output
| product_name | revenue | units_sold | revenue_per_unit |
|---|---|---|---|
| Laptop | 90000 | 3 | 30000 |
| Mouse | 5000 | 10 | 500 |
| Keyboard | 12000 | 0 | NULL |
| Monitor | 30000 | 5 | 6000 |
Why does NULLIF work?
"NULLIF(value1, value2)" compares two values.
- If both values are equal, it returns NULL.
- If they are different, it returns the first value.
For example:
SELECT NULLIF(0, 0); -- NULL
SELECT NULLIF(5, 0); -- 5
When "units_sold = 0", NULLIF returns NULL instead of zero.
SQL can divide by NULL without raising a divide-by-zero error.
That's why the Keyboard's revenue per unit becomes NULL.
What if you want 0 instead of NULL?
You can combine "COALESCE()" with "NULLIF()".
SELECT
product_name,
revenue,
units_sold,
COALESCE(
revenue / NULLIF(units_sold, 0),
0
) AS revenue_per_unit
FROM sales;
Now the result will show 0 when the calculation cannot be performed.
Remember, this does not mean the product actually earned zero revenue per unit. It simply means the calculation was not possible because no units were sold.
Practical takeaway
Use "NULLIF()" when you need to avoid division by zero.
It is useful in revenue reports, profit margins, conversion rates and percentage calculations.
A small function can prevent your SQL query from failing.
Your turn
What will this query return?
SELECT COALESCE(NULLIF(50, 50), 100);
Share your answer in the comments.
Follow Sankalan Data Tech for more practical SQL challenges.
Top comments (0)