Trying to divide the columns and it's returning error division by zero in SQL-CodePudding

I have sql query which will calculate the cumulative sum and etc. Below is the query

SELECT 
  GRP,  
  category, 
  price, 
  units, 
  CASE WHEN customers > 10 THEN customers ELSE 0 END AS customers_adj,
  1.00000 *(
    SUM(customers_adj) OVER(PARTITION BY grp, category ORDER BY 
        FIGURE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ))/ SUM(customers_adj) OVER (PARTITION BY grp, category) AS cum_max_price_cust
FROM 
  table_1

The issue is with the last column. It's returning error as SQL Error [100051] [22012]: Division by zero. Can anyone help me with this?

CodePudding user response：

you need this:

Select dividend / NULLIF(divisor, 0)

CodePudding user response：

You can use DIV0. It returns 0 when the divisor is 0 (rather than reporting an error).

SELECT 
  GRP,  
  category, 
  price, 
  units, 
  CASE WHEN customers > 10 THEN customers ELSE 0 END AS customers_adj,
  1.00000 * DIV0( (
    SUM(customers_adj) OVER(PARTITION BY grp, category ORDER BY 
        FIGURE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )), SUM(customers_adj) OVER (PARTITION BY grp, category) ) AS cum_max_price_cust
FROM 
  table_1;

https://docs.snowflake.com/en/sql-reference/functions/div0.html