Math division in SQL
Results 1 to 11 of 11

Thread: Math division in SQL

  1. #1
    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. #2
    Join Date
    Sep 2002
    Posts
    5,883
    You may need case statement, set result to something in case sum(ftotprice)=0.

  3. #3
    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. #4
    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. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,861
    Didn't you mean

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

  6. #6
    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. #7
    Join Date
    Jul 2006
    Posts
    5
    Quote 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. #8
    Join Date
    Sep 2002
    Posts
    5,883
    Can you post your statement?

  9. #9
    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. #10
    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. #11
    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
  •