Divide by Zero Error encountered in Snowflake Query

Question

I have the following snowflake query where I am getting a divide by zero error...Can you please help me here..

with cte1 as 
(select * from "coe.cup"
where typeofcare ='AM'
and status ='DONE'
and review ='false'
and date (assigneddate)>='2021-04-01'), cte2 as(
select cast(completed as date) completeddate ,iscode
 ,iff(iscode=1,datediff(minute,assigneddate,coded),0) codeddatetime
 ,iff(iscode=0,datediff(minute,assigneddate,qaed),0) qaeddatetime
 ,datediff(minute,assigneddate,completed) overall  from 
 (select *,iff(qaed='1900-01-01 00:00:00.0000000',1,0) iscode from cte1)a )
 select completeddate 
 ,sum(iff(iscode=1,1,0)) noofvisitbillscoded
 ,sum(iff(iscode=1,0,1)) noofvisitbillscodedandqaed
 ,count(1) totalvisitbillscompleted
 ,cast(sum(codeddatetime)/sum(iff(iscode=1,1,0)) as float)/60 averagetimeforcodedvisitbills
 ,cast(sum(qaeddatetime)/sum(iff(iscode=1,0,1)) as float)/60  averagetimeforcodedandqaedvisitbills
 ,cast(sum(overall)/count(1) as float)/60 overallaveragetime
 from cte2
 group by completeddate

score 4 · Answer 1 · answered Oct 13 '21 at 12:27

4

Another option is to to use the DIV0 function so something like:

DIV0(sum(codeddatetime),sum(iff(iscode=1,1,0))

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

answered Oct 13 '21 at 12:27

Dean Flinter

654
3
6

score 2 · Answer 2 · answered Oct 13 '21 at 18:54

When division is involved the divisor could be handled with NULLIFZERO:

NULLIFZERO( )

Returns NULL if the argument evaluates to 0; otherwise, returns the argument.

Second pattern sum(iff(iscode=1,1,0)) is a conditional sum that emulates filtered COUNT. It could be further simplfied with COUNT_IF

COUNT_IF( )

Returns the number of records that satisfy a condition.

To sum up:

,cast(sum(codeddatetime)/sum(iff(iscode=1,1,0)) as float)/60 

=>
,SUM(codeddatetime)/NULLIFZERO(COUNT_IF(iscode=1))/60 

=> if iscode is boolean column then:
,SUM(codeddatetime)/NULLIFZERO(COUNT_IF(iscode))/60

score 1 · Accepted Answer · answered Oct 13 '21 at 11:23

I assume that this happens due to the /sum(iff(iscode=1,1,0)) where this presumably sometimes returns 0.

One aproach to deal with division by zero is to use NULLIF

NULLIF( <expr1> , <expr2> )

returns NULL if expr1 is equal to expr2, otherwise returns expr1.

So, in your code where you have, for example sum(iff(iscode=1,1,0)), you can replace this with:

NULLIF(sum(iff(iscode = 1, 1, 0)), 0)

and this should then return NULL instead of causing a division by zero.

Divide by Zero Error encountered in Snowflake Query

3 Answers3