bigQuery throws "division by zero: 0 / 0" error

user_12454540 0
Viewed 26008

I am doing the percentage by the frequency of column value using bigquery. However, some of the value might be zero, so the query will return error for sure

(division by zero: 0 / 0)

How to apply kind of IFERROR(x/y,null) in this case? so the query will bounce null value as the result instead of error?

SELECT 
    User_ID, 
   ROUND(SUM(CASE WHEN Name LIKE '%MIKE%' THEN 1 ELSE 0 END) / COUNT(Name) * 100 ,1) AS Percentage_of_MIKE,
    
FROM
  table
GROUP BY 
  User_ID

TRIED:

ROUND(SAFE_DIVIDE(SUM(CASE WHEN Name LIKE '%MIKE%' THEN 1 ELSE 0 END) / COUNT(Name) * 100 ,1)) AS Percentage_of_MIKE,
google-bigquery count select group-by sql
3 Answers

You can just use SAFE_DIVIDE function in such cases

Something like in below example

ROUND(SAFE_DIVIDE(SUM(CASE WHEN Name LIKE '%MIKE%' THEN 1 ELSE 0 END), COUNT(Name) * 100) ,1) AS Percentage_of_MIKE
user_52219440

I tend to use NULLIF() for this purpose, because I like using the division operator for division:

SELECT User_ID, 
       ROUND(COUNTIF(Name LIKE '%MIKE%') * 100 / NULLIF(COUNT(Name), 0), 1) AS Percentage_of_MIKE
FROM table
GROUP BY User_ID;
user_11440350

This error indicates that you have User_IDs whose all Names are NULL. So the denominator of your division is 0 (COUNT(Name) counts non-null values of Name), and you get the division by 0 error.

A simple way to avoid this is to use AVG():

ROUND(AVG(CASE 
    WHEN Name LIKE '%MIKE%' THEN 1.0
    WHEN Name IS NOT NULL THEN 0
END) * 100, 1) AS Percentage_of_MIKE
user_106767160
Related