Converting a number to a percentage (using convert(decimal,......

[handlebars]

I have the following line in sql:

CAST(CONVERT(int, dbo.ResolvedInTargetCount.[Resolved In Target]) / CONVERT(int, dbo.CallsResolvedCount.[Calls Resolved Count]) AS decimal(3, 3)) AS [% Resoved in Target]

but when i go to run it, i get the error message:

"arithmetic overflow error converting numeric to data type numeric"

The reason i am trying to convert it is because before I was only getting number rounded up to 1 or down to 0!

Any help would be apprecaited.

Andrew

 

[JamesLean]

The easiest way to get a proper percentage from two ints is this:

DECLARE @a int, @b int

SELECT @a = 1, @b = 3

SELECT 100.0 * @a / @b

--James

 

[handlebars]

Thanks for you post James

It is an ok work around, but how do i atually get a decimal from two intergers (i cannot belive it is so difficult!!!)

Andrew

 

[donutman]

Go back to sleep.

 

[y3by]

hi,
from my little experience in TSQL, will need a cast to float, because when there are int involved in multiplication, then results are presented in int format...

i hope you understand my eng
good work

 

[SQLSister]

NO don;t cast to float. Float is an inexact dattype, you will get calcluation rounding problems.

Mutiply the first int by 1.0 which will make the top number a decimal and then the calculation will be done in decimals.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

I don't think any decimal type will solve the problem of percentages not adding up to 100%...you always have to supply a fudge factor for that.
If that isn't what you were referring too, SQLSister, then you may need therapy for that post traumatic stress syndrome you're exhibiting do to your old employer!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[handlebars]

SQLSister

Could you give me an example of code, would it be:

CONVERT(decimal,(2,2) (dbo.ResolvedInTargetCount.[Resolved In Target]) * 1.0) / (dbo.CallsResolvedCount.[Calls Resolved Count] * 1.0))

Many thanks

Andrew

 

[SQLSister]

That would work, although you don't need the second *1.0 at all. And the convert function is only necessary if you want to specifically limit to two decimal places. So this should work as well:

dbo.ResolvedInTargetCount.[Resolved In Target]) * 1.0 / dbo.CallsResolvedCount.[Calls Resolved Count]

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[JamesLean]

Isn't this exactly what I originally said (albeit I multiplied by 100.0 to get the actual percentage)?! (-:

--James

 

[donutman]

I was going to say that for you 4 days ago, but then I read the 3rd post and thought he might now be saying he just wants a decimal value (not a percentage).
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[handlebars]

Donutman

I have had a good sleep now I have a report in which I want do display percentages, so I need the result of the query to be a decimal and display it on the form as a percentage.

The first post was a good workaround, but meant that i could not have the % on my report, which the boss really wanted for some strange reason!

Andrew

 

[JamesLean]

Do you mean you actually want to display the '%' character in the results?

SELECT CAST(CONVERT(decimal(8, 2), 100.0 * ColA / ColB) AS varchar) + '%' AS percentage FROM Table1

--James

 

[handlebars]

My mistake, pretty damn obvious!

Many thanks

Andrew

 

[donutman]

As the link says:
Thank JamesLean
for this valuable post!