Divide by Zero Error

Hi All,

I have a query that runs two subqueries to get a percentage.  The SQL text is below.  I need to find a way to make the query just return zero if the divisor will be equal to zero.

With the line below I am getting the following error...

"IIF Function without () in query expression 'iif(select count(*) from datefilter_reports where takeoff_check =1) = 0.'"

Thanks!

G

 iif(select count(*) from datefilter_reports where takeoff_check =1) = 0,0, ((select count(*) from datefilter_reports where takeoff_methodology_RD =1)/(select count(*) from datefilter_reports where takeoff_check = 1)) AS AvgOfTakeoff_Methodology_RD
LVL 1
graysocAsked:
Who is Participating?
 
NestorioCommented:
Try this:

select
sum(iif(takeoff_methodology_RD =1, 1, 0))  as a, sum(iif(takeoff_check =1, 1, 0)) as b, iif(b=0, 0, a / b) as AvgOfTakeoff_Methodology_RD
from  datefilter_reports
0
 
NestorioCommented:
Try this,

iif((select count(*) from datefilter_reports where takeoff_check =1) = 0,0, ((select count(*) from datefilter_reports where takeoff_methodology_RD =1)/(select count(*) from datefilter_reports where takeoff_check = 1)) AS AvgOfTakeoff_Methodology_RD
0
 
graysocAuthor Commented:
That got rid of the () error, but now I have a syntax error, missing operator.  I was getting the same error before.  Maybe this just won't work with subqueries?

G
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
graysocAuthor Commented:
Perfect.  Thank you.

Grayson
0
 
graysocAuthor Commented:
One question...  How can I make the A and B not visible in the query?  Whatever results return on the query will end up in an excel sheet.

G
0
 
NestorioCommented:
This should be working,

select
iif(sum(iif(takeoff_check =1, 1, 0))=0, 0, sum(iif(takeoff_methodology_RD =1, 1, 0)) / sum(iif(takeoff_check =1, 1, 0))) as AvgOfTakeoff_Methodology_RD
from  datefilter_reports
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.