Getting the "division by zero" error in SQL? This guide explains what causes this error and how to fix it with practical examples.
What Causes This Error?
The division by zero error (22012) occurs when:
- Dividing by a column that contains zero values
- Calculating percentages without handling zero totals
- Aggregate functions returning zero in denominator
How to Fix It
- Use NULLIF(divisor, 0) to convert zero to NULL (returns NULL instead of error).
- Use CASE WHEN to check for zero before dividing.
- Filter out zero values with WHERE clause.
- Use COALESCE to provide default value when result is NULL.
Example: Wrong vs Correct
❌ Code That Causes the Error
SELECT revenue / quantity AS unit_price FROM orders; -- Error: division by zero when quantity = 0
✅ Corrected Code
SELECT revenue / NULLIF(quantity, 0) AS unit_price FROM orders; -- Correct: returns NULL instead of error when quantity = 0
Quick Checklist
- [ ] Verify column/table names are spelled correctly
- [ ] Check data types match expected values
- [ ] Review query syntax for missing keywords
- [ ] Ensure referenced tables/columns exist
Related SQL Errors
If you're troubleshooting SQL errors, you might also encounter: