
Math division in SQL
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?
Thanks.

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

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?

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.

Didn't you mean
CASE WHEN Sum(aritem.ftotprice) = 0 THEN 1

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.

Originally Posted by 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.
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

Can you post your statement?

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

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

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?
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

