Results 1 to 11 of 11

050913, 21:39 #1Registered User
 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 passthrough, making declaring any variables difficult.

050913, 22:45 #2Registered User
 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.
Anyway,
please try
100.00 * SUM(AVOIDABLE_FLAG) / NULLIF(COUNT(P_CODE) , 0) AS AVOIDABLE_RATE /* in percentage */

050913, 22:49 #3Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54The 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
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

051013, 13:47 #4Registered User
 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.

051013, 13:53 #5Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Both 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" ?
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

051013, 15:25 #6Registered User
 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?

051013, 15:30 #7Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Aha! You want the decimal value, not the percentage! Change the 1e2 to 1e0 and you will get a decimal percentage.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

051013, 15:37 #8Registered User
 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?

051013, 16:01 #9:)
 Join Date
 Jun 2003
 Location
 Toronto, Canada
 Posts
 5,516
Provided Answers: 1

051013, 16:23 #10Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54n_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.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

051013, 17:40 #11Registered User
 Join Date
 May 2013
 Posts
 6
Makes sense. Thanks so much!