Results 1 to 10 of 10
Thread: Percentage calculation

021412, 10:37 #1Registered User
 Join Date
 Oct 2011
 Posts
 11
Unanswered: Percentage calculation
Hello.
After a few hours of tearing my hair out, i'm afraid i have to ask for some of your valuable time in helping me...
I have a table with 3 columns; Site, Live, Target. I have a stored procedure which creates a temporary table and totals the data. Please see below for an example:
Code:Site Live Target 0LO 2 2 1BL 1 5 2XA 3 4 3PT 0 0 4FH 0 2 5KL 1 0
Code:Site Live Target Conversion 0LO 2 2 100% 1BL 1 5 20% 2XA 3 4 75% 3PT 0 0 ??% 4FH 0 2 0% 5KL 1 0 ??%
Additionall, for sites 3PT and 5KL, i'm actually not sure what conversion % is mathematically and logically correct... My head is frazzled!
Can someone save me?
Thank you in advance!
Tom.

021412, 11:08 #2Registered User
 Join Date
 Dec 2008
 Location
 At work...
 Posts
 92
Put the calculation in a caseexpression.
CASE WHEN Target <> 0 then ...
ELSE ... END
EDIT: When Target is 0, I'd like to return NULL. (And then you can skip the ELSE part of the CASE.)

021412, 11:10 #3Registered User
 Join Date
 Oct 2011
 Posts
 11

021412, 11:18 #4Registered User
 Join Date
 Dec 2008
 Location
 At work...
 Posts
 92
If you are using integer data types for GoneLive and Target, the calculation will be done "integer wise", i.e. only the integer part of the division is returned (which is 0 in your cases.)
To avoid this behavior, multiply [GoneLive] with 1.0 and your calculation will be done as you expect. (Or adjust 100 to 100.0, or start with 100 *)

021412, 11:19 #5Registered User
 Join Date
 Feb 2008
 Location
 Japan
 Posts
 3,483
It may be slightly different on each RDBMS.
In DB2, use the expression
COALESCE( Live * 100. / NULLIF(Target , 0) , 0 )
(I think this is an idiomatic expression to calculate percentage for INTEGERs on DB2.)
Divide by zero is not possible(not defined)
for mathematical "field" algebraic system(i.e. system on which addition/subtraction/multiplication/division were difined).
So, I thought to return NULL or 0 for divided by 0 would be practical.
Return NULL: Live * 100. / NULLIF(Target , 0)
Return 0: COALESCE( Live * 100. / NULLIF(Target , 0) , 0 )Last edited by tonkuma; 021412 at 11:31. Reason: Add NULL for divide by 0

021412, 11:34 #6Registered User
 Join Date
 Oct 2011
 Posts
 11
Spot on, that did just the trick! I had a slight issue with my update query, where it looked like it was recording the wrong value. However this was a rounding issue, i've now changed the 'Conversion' field to a decimal, reran the update and all is i would expect.
Thanks for your help! Much appreciated!

021412, 11:44 #7Registered User
 Join Date
 Feb 2008
 Location
 Japan
 Posts
 3,483

021412, 11:51 #8Registered User
 Join Date
 Oct 2011
 Posts
 11
To me, accuracy of data calculation is the most important thing. If a calculation is 5.88%, i need THAT to be displayed rather than 6%.
Therefore, "sum(capacity) * 100. / NULLIF(count(deployments) , 0)" will be in my script.
Thanks for your help!
I will do some research myself, but could you give me your understanding on what "COALESCE" does as a function? I've never come across it before, and after trying lots of different methods to resolve this issue, it was the golden ticket.

021412, 11:55 #9Registered User
 Join Date
 Dec 2008
 Location
 At work...
 Posts
 92
COALESCE(x1,x2,...,xn)
is equivalent to:
CASE WHEN x1 IS NOT NULL THEN x1
ELSE COALESCE(x2,...,xn) END
I.e. the COALESCE expression returns the value of the first nonNULL operand, found by working from left to right, or NULL if all the operands equal NULL.

021412, 12:28 #10Registered User
 Join Date
 Oct 2011
 Posts
 11
Thanks JarlH!