Percentage Calculation giving wrong result

Question

Post reply

Percentage Calculation giving wrong result

dharmabhushan

SSC Veteran

Points: 224
More actions
September 1, 2008 at 6:50 am

#192982

I have problem calculating Percentage value ?
suppose we have two column column_A & Column_B in a table having both column as bigint datatype.
I want result of "Select (Column_A*100 /Column_B) As ACS_PCT from Table_A" in fraction .But instead of giving correct fractional value it is wrong value.
e.g
Create Table Table_A
(
Column_A bigint,
Column_B bigint
)
INSERT INTO Table_A
(
Column_A,
Column_B
)
VALUES
(
148473,
191466379
)
Select (Column_A * 100 /Column_B) As ACS_PCT
from Table_A
----------------------------
Result
----------------------------
0
(1 row(s) affected)
Please provide immidiate solution

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738140 More actions · Answer 1

Please watch the forum title and post in the appropriate forum. Moved to T-SQL forum.

I'm not sure what you're calculating there, but it's not a percentage.

Percentage is (valuea/valueb) * 100.

dharmabhushan SSC Veteran Points: 224 More actions · Answer 2

Sorry steve,

(A/B)*100 or (A*100)/B the result should same and I m sure this is percentage calculation formula.We are recently facing this problem earliar everything was fine.

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

I think the problem you are facing is that an int / int produces an int. Take the following examples...

select 3/2

select 3/2.0

To fix the issue multiply one of your columns by 1.0.

Select 3/(2*1.0)

dharmabhushan SSC Veteran Points: 224 More actions · Answer 4

Thank you Ken for immidiate solution

I was struggling whole day to find out solution

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 5

To be on the safe side, do takle care of the '(' and ')' when using mathematical expression.

Its not the case in your scenario, but COULD lead you to confusion if you do not use them...

Select 100/(2*10)

Select (100/2)*10

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

samkaplan SSC Veteran Points: 225 More actions · Answer 6

Steve,

I found your response of interest as I was faced with a similar problem 6 months ago and ended up using a lot of CAST() as decimal in the formulas to make it work. Does 1.0 give accuracy to the integer level, one decimal point, or any degree? If I wanted precision to 2 decimals, would I multiply by 1.00 ?

Thanks,

Sam

tfifield SSCrazy Eights Points: 9655 More actions · Answer 7

To avoid the Integer zero problem I usually just CAST one side as something other than Int - MONEY seems to work just fine unless you are rounding (MONEY has problems with rounding). Something like

SELECT (CAST(FirstInt AS MONEY) * 100) / SecondInt

Todd Fifield

Percentage Calculation giving wrong result

Cookies on SQLServerCentral