Menu
I have a SQL query which used to cause a
Divide By Zero exception
I've wrapped it in a CASE statement to stop this from happening. Is there a simpler way of doing this?
Here's my code:
Percentage = CASE WHEN AttTotal <> 0 THEN (ClubTotal/AttTotal) * 100 ELSE 0 END
435
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.
We can avoid this error message using the following three methods: Using NULLIF() function. Using CASE statement. Using SET ARITHABORT OFF.
Use IFNULL or COALESCE() function in order to convert MySQL NULL to 0. Insert some records in the table using insert command. Display all records from the table using select statement.
A nicer way of doing this is to use NULLIF like this:
Percentage = 100 * ClubTotal / NULLIF(AttTotal, 0)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
