# Miscellaneous > Ask an Expert >  Math division in SQL

## flying_nun

I have this stmt as part of the SELECT:

SUM(aritem.fshipqty*aritem.fcost)/Sum(aritem.ftotprice) AS 'MatlCost %'

Sometimes sum(ftotprice)=0, which returns an error that SQL can't divide by 0.  The ftotprice field is never Null.

How do I get around this?    :Confused:  

Thanks.

----------


## rmiao

You may need case statement, set result to something in case sum(ftotprice)=0.

----------


## flying_nun

I'm going to need some more help.

I don't know SQL all that well.  What would I need to do to put a case statement in?

----------


## nosepicker

CASE WHEN Sum(aritem.ftotprice) = 0 THEN 0 
ELSE SUM(aritem.fshipqty*aritem.fcost)/Sum(aritem.ftotprice) END AS 'MatlCost %'

This is assuming that you want the value to be zero in the event that Sum(aritem.ftotprice) = 0.

----------


## skhanal

Didn't you mean

CASE WHEN Sum(aritem.ftotprice) = 0 THEN 1

----------


## nosepicker

It depends.  In this case, I used the CASE statement on the entire expression, not just the denominator.  Like I stated before, it depends on what value you want to use when the denominator is zero.  If you want the value to be equal to the numerator, then you can use the CASE statement you wrote and put it in the denominator.  If you want the entire value to be equal to zero, then you can use the one I wrote.

----------


## flying_nun

> CASE WHEN Sum(aritem.ftotprice) = 0 THEN 0 
> ELSE SUM(aritem.fshipqty*aritem.fcost)/Sum(aritem.ftotprice) END AS 'MatlCost %'
> 
> This is assuming that you want the value to be zero in the event that Sum(aritem.ftotprice) = 0.



Okay, I tried inserting the CASE stmt into the SELECT and that didn't work.  Where do I put this?

I told you I don't know very much about SQL    :Embarrassment:

----------


## rmiao

Can you post your statement?

----------


## flying_nun

SELECT armast.finvdate, aritem.fcinvoice, aritem.fcustno, aritem.fsalesacc, Sum(aritem.ftotprice) AS 'NetSales', SUM(aritem.fshipqty*aritem.fcost) AS 'MatlCost', SUM(aritem.fshipqty*aritem.fcost)/Sum(aritem.ftotprice) AS 'MatlCost %'
FROM data01.dbo.aritem aritem, data01.dbo.armast armast
WHERE armast.fcinvoice = aritem.fcinvoice AND ((armast.finvdate Between ? And ?) AND (aritem.fsalesacc='xxxxxxx'))
GROUP BY armast.finvdate, aritem.fcinvoice, aritem.fcustno, aritem.fsalesacc
ORDER BY armast.finvdate, aritem.fcinvoice

----------


## nosepicker

SELECT armast.finvdate, aritem.fcinvoice, aritem.fcustno, aritem.fsalesacc, Sum(aritem.ftotprice) AS 'NetSales', SUM(aritem.fshipqty*aritem.fcost) AS 'MatlCost', CASE WHEN Sum(aritem.ftotprice) = 0 THEN 0 
ELSE SUM(aritem.fshipqty*aritem.fcost)/Sum(aritem.ftotprice) END AS 'MatlCost %' 
FROM data01.dbo.aritem aritem, data01.dbo.armast armast
WHERE armast.fcinvoice = aritem.fcinvoice AND ((armast.finvdate Between ? And ?) AND (aritem.fsalesacc='xxxxxxx'))
GROUP BY armast.finvdate, aritem.fcinvoice, aritem.fcustno, aritem.fsalesacc
ORDER BY armast.finvdate, aritem.fcinvoice

----------


## flying_nun

Tried that.  Get this:  parameters are not allowed in queries that can't be displayed graphically.

Does it matter that I'm using MS Query out of Excel and trying to modify the system generated SQL stmt?

----------

