Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Wink Unanswered: SQL CASE statement

    Hi Ive got a simple query where I want to calculate an average of one number divided by the other ie: avg(x / y)

    Im trying to use a case statement to return 0 in the event that y is 0, to avoid a division by zero error. My query is still returning a division by zero error anyway can anybody help?

    SELECT CCode, CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE AVG(BSCLTradeCreditors / BS_TOTAL_ASSETS) END AS myaverage
    FROM [Company/Year]
    GROUP BY CCode, BS_TOTAL_ASSETS

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this (untested) --

    SELECT CCode
    , AVG(CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE BSCLTradeCreditors / BS_TOTAL_ASSETS END) AS myaverage
    FROM [Company/Year]
    GROUP BY CCode
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    10

    Perfect

    Thanks, your code worksed like a charm. I just needed to cast the two feilds to floats so that my division gives a result as a float rather than 1 or 0.

    Thanks again

    Dav

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but you might want to rethink your statistics. AVG(A/B) can lead to skewed results if the values have a high variance. For instance, given these values for Creditors(A) and Assets(B)

    A B
    9 10
    100 1000

    ..AVG(A/B) = 0.5! Record 1, with less than 1% of the assets has had 50% influence on the result. For most businesses purposes you would calculate your average as SUM(BSCLTradeCreditors)/SUM(BS_TOTAL_ASSETS). In the odd case where SUM(BS_TOTAL_ASSETS) is zero, (a very bad year?), you would use:

    SELECT CCode,
    CASE WHEN SUM(BS_TOTAL_ASSETS) = 0 THEN 0.0 ELSE SUM(BSCLTradeCreditors)/SUM(BS_TOTAL_ASSETS) END CASE) AS myaverage
    FROM [Company/Year]
    GROUP BY CCode

    -or-

    SELECT CCode,
    SUM(BSCLTradeCreditors)/NULLIF(SUM(BS_TOTAL_ASSETS), 0) as myaverage
    FROM [Company/Year]
    GROUP BY CCode

    ...which (more accurately) yeilds NULL when then mathematical result is undefined.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •