resolve devide by zero error

I have a procedure calling some data from a view which has the where clause to Eliminates zero values, but I still get Devide by zero error. also, can anyone provide a better syntaxt to eliminate those zero's in one query?
code attached.  I would basically like extracked a list of Items where the usage for this period have doubled what the last 2 months were and M1 is more than half of what we have.


select * from vw_spike_usages2 
group by item_id ,qty_free,M3,M2,M1
having  M1/M2+M3>=1 and M1/qty_free >=.5

Open in new window

tips54Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
I have no idea what your expression mean but are you sure that ....
 M1/M2+M3>=1
is correct and that you don't mean....
 M1/(M2+M3)>=1
which is quite different as a mathematical expression.
0
tips54Author Commented:
thanks.
I added the parenthesis.  Can you think of any way to address the divide by zero error?
0
peter57rCommented:
What does this mean then?
'data from a view which has the where clause to Eliminates zero values'

or are you just testing individual field values and not removing records where m2+m3 is zero?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Christopher GordonSenior Developer AnalystCommented:
Not sure if I followed your example, but using Nullif([fieldname],0) always resolved this issue for me.  So in your example,

M1/NULLIF((m2+m3),0) > = 1
0
ksparkyCommented:
You might try this:

SELECT *
FROM vw_spike_usages2
WHERE ISNULL(M1,-1)>0 AND ISNULL(M2,-1)>0 AND ISNULL(M3,-1)>0
AND (M1/M2)+M3>=1 AND M1/qty_free>=.5
GROUP BY item_id, qty_free, M3,M2,M1

I am quite confident this will eliminate the divide by zero as it eliminates all of the zeroes from the query by using -1
0
tips54Author Commented:
Ksparky,
the statement did not resolve the divide by zero error.
I resolved this some other way
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.