# Thread: Math division in SQL

1. Registered User
Join Date
Jul 2006
Posts
5

## 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.

2. Moderator
Join Date
Sep 2002
Posts
5,938
You may need case statement, set result to something in case sum(ftotprice)=0.

3. Registered User
Join Date
Jul 2006
Posts
5
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?

4. Registered User
Join Date
Dec 2004
Posts
502
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.

5. Experts
Join Date
Nov 2002
Location
New Jersey, USA
Posts
3,903
Didn't you mean

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

6. Registered User
Join Date
Dec 2004
Posts
502
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.

7. Registered User
Join Date
Jul 2006
Posts
5
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

8. Moderator
Join Date
Sep 2002
Posts
5,938

9. Registered User
Join Date
Jul 2006
Posts
5
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

10. Registered User
Join Date
Dec 2004
Posts
502
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

11. Registered User
Join Date
Jul 2006
Posts
5
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
•

×