CODEWITHSUNDEEP
sqlgoogle-bigquery
flyingL123
flyingL123

Reputation: 8096

Division by 0 error in BigQuery even when short-circuiting should prevent it

In my data, the fulfillment_ratio field may or may not be equal to 0. I can't figure out why I am still getting division by 0 errors. I thought I was short-circuiting using an IF statement so that the calculation will not take place if a 0 exists for the given group.

SELECT
    order_key,
    order_source,
    sale_item.key AS key,
    ANY_VALUE(sale_item.name) AS name,
    ANY_VALUE(sale_item.sku) AS sku,
    ANY_VALUE(sale_item.quantity) * MIN(fulfillment_ratio) AS quantity_counted,
    MIN(sale_item.base_price) AS price,
    IF(COUNTIF(fulfillment_ratio = 0) > 0, 0, SUM(cogs / fulfillment_ratio) * MIN(fulfillment_ratio)) AS cost_of_goods_sold
  FROM 
    order_product_stockable_items
  GROUP BY
    order_key,
    order_source,
    sale_item.key

As you can see in the last line of the SELECT list, I'm counting to see if there are any cases where fulfillment_ratio = 0, if there are I was under the impression that 0 would be returned and the false condition would not be evaluated. However, I still get a division by 0 error. How is that possible? Is there a better way to do this?

Upvotes: 0

Views: 2898

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175944

You could use NULLIF:

COALESCE(SUM(cogs / NULLIF(fulfillment_ratio,0)) * MIN(fulfillment_ratio),0)

Upvotes: 1

Related Questions