Suppose you want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when it tries to calculate ratio for the Lord of the Rings Club, which has no women:
SELECT club_id, males, females, males/females AS ratio
You can use the function NULLIF to avoid division by zero. NULLIF compares two expressions and returns null if they are equal or the first expression otherwise.
Rewrite the query as:
SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
Any number divided by NULL gives NULL, and no error is generated.
Be sure to look back over my posts this week
for more SQL tips.
And for more on SQL, be sure to check out my book SQL: Visual QuickStart Guide, 3rd Edition.