Results 1 to 11 of 11
  1. #1
    Join Date
    May 2013
    Posts
    6

    Unanswered: Dividing but getting 0's in SQL Statement

    I'm using a subquery to pull out sums and counts, and trying to divide one number by another. The results I get when I do the division are all 0's, except in cases where the divided numbers are 1 and 1, thus giving me a 1 back.

    Here is my structure

    SELECT
    SALES_ID,
    COUNT(P_CODE) AS "TOTAL WORK ORDERS",
    SUM(AVOIDABLE_FLAG) AS AVOIDABLE,
    SUM(AVOIDABLE_FLAG)/COUNT(P_CODE) AS AVOIDABLE_RATE
    FROM (****subquery here) A

    GROUP BY SALES_ID


    How can I get the "Avoidable Rate" to come back in a decimal format, or even a percentage if that is possible. I'm relatively new to SQL and haven't quite figured this one out yet. I'm pulling from a db2 server and using MS ACCESS to pass-through, making declaring any variables difficult.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How can I get the "Avoidable Rate" to come back in a decimal format, or even a percentage if that is possible.
    I might answered the question before. But, I can't remember in what thread.

    Anyway,
    please try
    100.00 * SUM(AVOIDABLE_FLAG) / NULLIF(COUNT(P_CODE) , 0) AS AVOIDABLE_RATE /* in percentage */

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem is most likely the need for a datatype conversion. If that is your problem, try using:
    Code:
    SELECT SALES_ID
    ,  COUNT(P_CODE) AS "TOTAL WORK ORDERS"
    ,  SUM(AVOIDABLE_FLAG) AS AVOIDABLE
    ,  1e2 * SUM(AVOIDABLE_FLAG) / COUNT(P_CODE) AS AVOIDABLE_RATE
       FROM (****subq ery here) A
    
       GROUP BY SALES_ID
    If this fixes your problem, come back for an explanation. If it does not, come back with what's wrong and we'll try again!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    May 2013
    Posts
    6
    Both of those solutions worked to get me accurate numbers, but is there a way to format those as a percent?

    Thanks for the responses.. very much helpful.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Both ought to display the compuation as a percentage (a number between 0 and 100 with decimal places instead of a decimal between 0 and 1). What do you mean when you ask for "format those as a percent" ?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    May 2013
    Posts
    6
    For example in my result set I have 1 avoidable and 6 in the total work orders column. It comes back as 16.66667. Is there a way to get it to come back as 0.1666667?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Aha! You want the decimal value, not the percentage! Change the 1e2 to 1e0 and you will get a decimal percentage.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    May 2013
    Posts
    6
    That was it! Thank you Pat. Can I ask, what is the significance of 1e2 and 1e0, what do they mean?

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by bosslx View Post
    what is the significance of 1e2 and 1e0, what do they mean?
    These are what is called scientific notation for 100 (1 * 10^2) and 1 (1 * 10^0) respectively.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    n_i is correct... The 1e2 is scientific notation for 100 as a real number, multiplying by that converted your count to a real number which when divided by the integer count stayed a real number so the result was 16.6666667 which was the percentage that you wanted with the decimals intact.

    When I discovered that you wanted the decimal value (between 0 and 1) instead of the percentage (between 0 and 100), the simplest way to get from one to the other was to change the exponent.

    Note that the conversion from integer to real is the most important part of the change, the scaling (the difference between 1 and 100) was almost a side effect. You could get the same effect using a CAST(), this was just the easier way to get what you wanted.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    May 2013
    Posts
    6
    Makes sense. Thanks so much!

Posting Permissions

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